Naming Worksheets with a Macro

[Guest]

I am trying to use a macro to create a copy of a worksheet. After inserting
sheet1 I can go it (worksheet("sheet1").select) then rename it to a standard
name. However, when I run the macro again, the next new sheet is now called
sheet2 which interferes with the macro that is looking for Sheet1. Is there
a way to use a formula to rename worksheets.

 

[Doug Glancy]

Barry,

This copies "Sheet1" (in the book with the macro) and renames the copy.
Change ThisWorkbook to ActiveWorkbook or other workbook name if the sheet to
be copied is not in the book with the macro:

Sub test()

With ThisWorkbook
.Worksheets("Sheet1").Copy after:=.Worksheets(.Worksheets.Count)
ActiveSheet.Name = "MySpecialName" 'the new copy is now the active sheet
End With

End Sub

hth,

Doug