make copies of sheet and name them from list

[Robert]

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

 

[Gord Dibben]

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

 

[Don Guillett]

Why not just use data>filter>autofilter?

 

[Shane Devenshire]

Hi,

Well I finally get a use for this feature:

1. Higlight the employee numbes in Column A and choose Data, Pivot Table and
Pivot Chart Report, click Next, Next and choose Existing worksheet and pick
an empty range starting on row 3 or lower, say D4, and click Finish.
2. Drop the title for the Employee # field in the Page Field area.
3. With the pivot table selected choose PivotTable, Show Pages and click OK.

4. Since you probably don't need the pivot tables, select the first employee
sheet tab, how down the shift key and click the last employee sheet tab.
5. Select the range with the pivot tables (empty ones) and press Delete.

 

[Robert]

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