New Worksheets Macro
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
|