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.