Mike, there are 3 ways to reference a sheet:
by tab name - Sheets ("New Sht Name").Activate
by the index number of the Sheets Collection - Sheets(2). Activate
by the code name of the sheet - Sheet2.Activate
It is not a good idea to change the code name of a sheet for Excel's sake,
but instead work with the other 2 properties. The Sheet.Name is the name you
see on the tab, and the index number is the order in which they appear from
left to right. Using these properties you can position any sheet wherever
you want.
Study the following code which will move the first tab to the end tab each
time it is ran,

Sub shts()
Dim i As Integer
Dim ws As Worksheet, wsName As String

i = Worksheets(1).Index
Set ws = Worksheets(i)
wsName = ws.Name
MsgBox ("The first sheet's name is " & wsName)
Sheets(1).Move after:=Sheets(Sheets.Count)
Set ws = Worksheets(i)
wsName = ws.Name
MsgBox ("The first sheet's name is " & wsName)
End Sub

Mike F
"michael.beckinsale" wrote in message
ups.com...
Hi All,

Is there a way to change the sheet name Excel assigns to a sheet using
VBA? If so would anybody be kind enough to provide a code snippet?

The code below copy's a sheet(s) and renames the tab but l want to
also rename the name assigned by Excel. A further problem is that l
dont know how to identify the sheet name that Excel has assigned to
the newly copied sheet!


For i = 1 To NoSheetsReqd
Sheets("My Template").Copy Befo=Sheets("2")
Sheets("My Template (2)").Select
Sheets("My Template (2)").Name = "New Sht Name"
Next i

All help gratefully appreciated.

For the curious l am building a solution where complete control of the
worksheets order & positioning is needed but it is desirable for the
end-user to be able to change the tab name to make it easily
identifiable to them, ie not just a number, so l can then use Excels
assigned names to control the order, positioning etc.


Regards

MB