I just tested and the macro copied Template sheet 607 times based on a list
of names in A1:A607 on the sheet named List.

Perhaps you have a duplicate name in your source?


Gord


On Wed, 20 Aug 2008 08:41:01 -0700, Noahthek
wrote:

Thank you very much.

It worked reasonably well the first time, stopping after about 50 or 60
worksheets. But then it stopped and no matter how many times I try to
recreate it I get the same error.

This is the section that causes the problem:

TemplateWks.Copy After:=Worksheets(Worksheets.Count)

I receive a run time error 1004; Method 'Copy' of Object'_Worksheet' failed

Maybe there's something I'm missing. I'm relatively new to coding although I
do create simple macros from time to time.

To answer the person who asked if filtering would be an option: Sadly, no.
This report is tracking hours worked on specific projects and each project
needs a separate worksheet. There are about 500 of them.

Thanks,
Noah

Below is the full code:

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

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

Set TemplateWks = Worksheets("MasterMatter")
Set ListWks = Worksheets("MasterAttorney")
With ListWks
Set ListRng = .Range("a7:a478", .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