Adding copies of a master

[Guest]

Within a workbook, is it possible to insert multiple copies of worksheets at
one time? I have a master and need to insert 20 copies within the workbook
to enter employee data.

Thanks.

 

[Gord Dibben]

How would you like to name those copied sheets?

You could put a list of names in a sheet called "List"

Say newsheet1, newsheet2, newsheet3 down to A20 then run this macro from Dave
Peterson.

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: Master
' and name the sheets accordingly

Dim TemplateWks As Worksheet
Dim ListWks As Worksheet
Dim ListRng As Range
Dim myCell As Range

Set TemplateWks = Worksheets("Master")
Set ListWks = Worksheets("List")
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