I want to use VBA to rename 8 sheets in a workbook with
the names of employees stored in a range in one worksheet.
I can do this by refering to the existing name of the
sheet but how can i refer to the sheets in order using
code.

i.e if sheet 1 is named PTurner, I can rename it by
refering to Sheets ("P Turner"), but if this name has been
changed to something else then an error occurs. I need the
code to recognise it as 'sheet1' whatever the name entered
currently may be. Can you tell how I can do this please.

You use the index to refer to the sheets in order.

For example, If you have 3 sheets - Mike, John and George -
then Sheets(1) is the same as saying Sheets("Mike"),
Sheets(2) is the same as Sheets("John"), and so on... The
index is 1-based, meaning that 1 is the first and 2 is the
second...

The only issue with doing it this way is that if the
user "accidentally" moves the order, then the indexes will
change. To get that to work, you could use the .Name
property. For example:

For x = 1 to Sheets.Count
If Sheets(1).Name = "Mike" Then
<do something
Exit For
end if
Next x

Just some ideas to get your gears workin'!

-----Original Message-----
I want to use VBA to rename 8 sheets in a workbook with
the names of employees stored in a range in one
worksheet.
I can do this by refering to the existing name of the
sheet but how can i refer to the sheets in order using
code.

i.e if sheet 1 is named PTurner, I can rename it by
refering to Sheets ("P Turner"), but if this name has
been
changed to something else then an error occurs. I need
the
code to recognise it as 'sheet1' whatever the name
entered
currently may be. Can you tell how I can do this please.
.

This code will copy "P Tuner" 8 times then rename it to what ehatever you
want.
This is if "P Tumer" is sheet(1).

Private Sub CommandButton1_Click()

For y = 1 To 8
Sheets("P Turner").Select
Sheets("P Turner").Copy After:=Sheets(1)
Sheets("P Turner (2)").Name = "Employee " & y
Next y

End Sub


"Jeremy" wrote in message
...
I want to use VBA to rename 8 sheets in a workbook with
the names of employees stored in a range in one worksheet.
I can do this by refering to the existing name of the
sheet but how can i refer to the sheets in order using
code.

i.e if sheet 1 is named PTurner, I can rename it by
refering to Sheets ("P Turner"), but if this name has been
changed to something else then an error occurs. I need the
code to recognise it as 'sheet1' whatever the name entered
currently may be. Can you tell how I can do this please.