Thanks Gord! It worked...there were a few errors "Please fix", but I just
kept cancelling and it took off and made all the sheets. Just for fun, I
tried to change the range to A6:A51 where the actual numbers are but I got VB
errors. Maybe the headers rows gave problems. I should have been more
specific. In any case, this saved me tons of time and I very much appreciate
it!
Robert

"Gord Dibben" wrote:

Edit sheet names to suit where noted.

Sub CreateNameSheets()
' by Dave Peterson
' List sheetnames required in col A in a sheet: List
' Sub will copy sheets based on the sheet named as: Template
' and name the sheets accordingly

Dim TemplateWks As Worksheet
Dim ListWks As Worksheet
Dim ListRng As Range
Dim mycell As Range

Set TemplateWks = Worksheets("Template") 'change to "Sheet2"
Set ListWks = Worksheets("list") 'Change to "Sheet1"
With ListWks
Set ListRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
End With

For Each mycell In ListRng.Cells
TemplateWks.Copy After:=Worksheets(Worksheets.Count)
On Error Resume Next
ActiveSheet.Name = mycell.Value
If Err.Number < 0 Then
MsgBox "Please fix: " & ActiveSheet.Name
Err.Clear
End If
On Error GoTo 0
Next mycell

End Sub


Gord Dibben MS Excel MVP

On Thu, 9 Jul 2009 16:01:04 -0700, Robert
wrote:

Hello,
I have about 50 employee numbers in sheet1 A:A. Is there code for a macro
that can make a copy of sheet2 for each instance of employee number and name
it with the employee number as the sheet name?

thanks in advance for any help,
Robert