Copy Active sheet & rename using part of Active sheet name

[Barb Mick]

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?

 

[My Aswer Is This]

Try this:

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

 

[Barb Mick]

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?

 

[My Aswer Is This]

Try this:

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

 

[Barb Mick]

Perfect. Thank you again.

 

[My Aswer Is This]

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.

Perfect. Thank you again.