Results 1 to 6 of 6

Thread: Copy Active sheet & rename using part of Active sheet name
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jan 2018
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Copy Active sheet & rename using part of Active sheet name

    I'm trying to find code to make a copy of the active sheet and rename the copy using part of the Active sheet name. For example if the Active sheet is named "Expenses" then I want the copy to be named "Expenses - West".

    Can anyone help?

  2. #2
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,805
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    13 Thread(s)

    Default Re: Copy Active sheet & rename using part of Active sheet name

    Try this:

    Code:
    Sub Copy_Active_Sheet()
    'Modified 1/29/2019 11:22:16 PM  EST
    Dim OldName  As String
    Dim NewName As String
    OldName = ActiveSheet.Name
    NewName = OldName & "-West"
    Sheets(OldName).Copy After:=Sheets(Sheets.Count)
    ActiveSheet.Name = NewName
    End Sub
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

  3. #3
    New Member
    Join Date
    Jan 2018
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy Active sheet & rename using part of Active sheet name

    This works beautifully, thank you!!!
    One more question...
    How can I insert the copy directly one tab right of the old sheet rather than at the end of the workbook?

  4. #4
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,805
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    13 Thread(s)

    Default Re: Copy Active sheet & rename using part of Active sheet name

    Try this:
    Code:
    Sub Copy_Active_Sheet()
    'Modified  1/31/2019  9:54:43 AM  EST
    Dim OldName  As String
    Dim NewName As String
    OldName = ActiveSheet.Name
    NewName = OldName & "-West"
    Sheets(OldName).Copy After:=Sheets(OldName)
    ActiveSheet.Name = NewName
    End Sub
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

  5. #5
    New Member
    Join Date
    Jan 2018
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy Active sheet & rename using part of Active sheet name

    Perfect. Thank you again.

  6. #6
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,805
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    13 Thread(s)

    Default Re: Copy Active sheet & rename using part of Active sheet name

    Glad I was able to help you.
    Come back here to Mr. Excel next time you need additional assistance.
    Quote Originally Posted by Barb Mick View Post
    Perfect. Thank you again.
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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