Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    How do i copy an existing sheet then rename the new one?

    Is there a sheet copy function in Excel?

  2. #2
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts
    [quote name='patt' post='766506' date='20-Mar-2009 23:10']How do i copy an existing sheet then rename the new one?

    Is there a sheet copy function in Excel?[/quote]
    shtNew = shtOld.Copy(After:=.Sheets(4))
    shtNew.Name ="Patt"

  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post
    Without code

    1. Press Ctrl and click on worksheet tab
    2. Still holding Ctrl, drag worksheet tab to right
    3. Right hand mouse click tab
    4. Click rename
    5. Type new name


    With Code

    Something like:

    Sub NewSheet()
    Dim strNewName

    strNewName = InputBox("New Sheet Name")

    Set NewSheet = Worksheets.Add
    NewSheet.Name = strNewName

    End Sub

    MTA Add doesn't copy
    Jerry

  4. #4
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post
    Try this for copying the active sheet:

    Sub CopySheet()
    Dim strNewName
    Dim intCount
    Dim WsName

    WsName = ActiveSheet.Name


    intCount = Worksheets.Count

    strNewName = InputBox("New Sheet Name")


    Sheets(WsName).Copy After:=Sheets(intCount)
    Sheets(WsName & " (2)").Name = strNewName

    End Sub
    Jerry

  5. #5
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    [quote name='StuartR' post='766507' date='21-Mar-2009 10:18']shtNew = shtOld.Copy(After:=.Sheets(4))
    shtNew.Name ="Patt"
    [/quote]

    Does the first command copy sheets(4) to a new sheet?
    Can it copy an existing sheet by name?
    Does the new sheet always go at the end of all sheets?
    Thanks for your help Stuart.

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    [quote name='Jezza' post='766508' date='21-Mar-2009 10:20']Without code

    1. Press Ctrl and click on worksheet tab
    2. Still holding Ctrl, drag worksheet tab to right
    3. Right hand mouse click tab
    4. Click rename
    5. Type new name


    With Code

    Something like:

    Sub NewSheet()
    Dim strNewName

    strNewName = InputBox("New Sheet Name")

    Set NewSheet = Worksheets.Add
    NewSheet.Name = strNewName

    End Sub

    MTA Add doesn't copy[/quote]
    Thanks Jezza, for the with and without code, this helps as i am getting more into excel these days.
    I need the code solution at this stage so i can do it from within Access.

  7. #7
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post
    [quote name='patt' post='766512' date='20-Mar-2009 23:46']Thanks Jezza, for the with and without code, this helps as i am getting more into excel these days.
    I need the code solution at this stage so i can do it from within Access.[/quote]

    The the code in my Post 766510 will be better as it copies the active sheet after the last one using After:=Sheets(intCount)
    Jerry

  8. #8
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    [quote name='Jezza' post='766510' date='21-Mar-2009 10:42']Try this for copying the active sheet:

    Sub CopySheet()
    Dim strNewName
    Dim intCount
    Dim WsName

    WsName = ActiveSheet.Name


    intCount = Worksheets.Count

    strNewName = InputBox("New Sheet Name")


    Sheets(WsName).Copy After:=Sheets(intCount)
    Sheets(WsName & " (2)").Name = strNewName

    End Sub[/quote]
    Thanks Jezza, but i dont need to copy an active sheet. Although this example is good for that.

    What i need is to copy an existing sheet to a new name that I will supply from a recordset on a break of Entity, this sheet will take the new Enitiry's name.

  9. #9
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts
    [quote name='patt' post='766511' date='20-Mar-2009 23:43']Does the first command copy sheets(4) to a new sheet?
    Can it copy an existing sheet by name?
    Does the new sheet always go at the end of all sheets?
    Thanks for your help Stuart.[/quote]
    What a lot of questions.

    The first command copies the sheet that is assigned to variable shtOld, placing the new copy after the fourth sheet in the workbook. To copy a sheet named "MySheet" to the end of the workbook you want something like the following syntax...

    Dim sht As Object
    With ActiveWorkbook
    .Sheets("MySheet").Copy After:=.Sheets(.Sheets.Count)
    .Sheets(.Sheets.Count).Name = "MyNewSheet"
    End With

  10. #10
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    [quote name='StuartR' post='766516' date='21-Mar-2009 11:03']What a lot of questions.

    The first command copies the sheet that is assigned to variable shtOld, placing the new copy after the fourth sheet in the workbook. To copy a sheet named "MySheet" to the end of the workbook you want something like the following syntax...

    Dim sht As Object
    With ActiveWorkbook
    .Sheets("MySheet").Copy After:=.Sheets(.Sheets.Count)
    .Sheets(.Sheets.Count).Name = "MyNewSheet"
    End With
    [/quote]

    I like to get all the answers in a single post, but if you insist i can ask one question per post.

    Thank you for your responses, they are very helpful.

    Do i presume that i can copy a sheet ("mysheet") after a specified sheet ("specifiedsheet") like this?
    .Sheets("MySheet").Copy After:=.Sheets("specifiedsheet")

  11. #11
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts
    [quote name='patt' post='766526' date='21-Mar-2009 02:40']Do i presume that i can copy a sheet ("mysheet") after a specified sheet ("specifiedsheet") like this?
    .Sheets("MySheet").Copy After:=.Sheets("specifiedsheet")[/quote]
    Yes

  12. #12
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    [quote name='StuartR' post='766529' date='21-Mar-2009 16:48']Yes[/quote]
    Thank you

  13. #13
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    [quote name='patt' post='766526' date='21-Mar-2009 13:40']I have the following code in place.
    With ActiveWorkbook
    .Sheets("firstsheet").Copy After:=.Sheets(.Sheets.Count)
    .Sheets(.Sheets.Count).Name = rs!hospdesc
    Set mySheet = .Sheets(.Sheets.Count)
    End With[/quote]
    I would like to know if i am correct in addressing my mySheet to the newly copied sheet?

    It appears to be overwriting the first sheet and not copying sheets after the first sheet called "firstsheet"

  14. #14
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    [quote name='patt' post='767188' date='25-Mar-2009 14:00']I would like to know if i am correct in addressing my mySheet to the newly copied sheet?

    It now says that the ActiveWorkbook has nothing in it, sometimes it works and other times it is ok.
    What sets the ActiveWorkbook to something?[/quote]

  15. #15
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    It seems to be ok, if i exit fro the form and come back in again, it seems to clear itself.

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •