copy sheets & name them from list

[Robert]

Hello,
I received this code from someone on this forum. It works on my excel 2007
at home with 2gigs of ram but fails with the message "run-time error 1004
method copy object_worksheet failed" on my work computer with excel 2003 with
1gig ram. (Both with XP) Could this be a resource issue, or a problem with
2003? Is there a workaround? There are about 50 sheets to make copies from
the list that is at A7:A51. The template sheet is in the same workbook and
has a lot of formulas on it. Here is the code:
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
Application.Calculation = xlCalculationManual
Dim TemplateWks As Worksheet
Dim ListWks As Worksheet
Dim ListRng As Range
Dim mycell As Range

Set TemplateWks = Worksheets("337") 'change to "Sheet2"
Set ListWks = Worksheets("Technicians") 'Change to "Sheet1"
With ListWks
Set ListRng = .Range("A7", .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
Application.Calculation = xlCalculationAutomatic
End Sub

thanks in advance for any help,
Robert

 

[ryguy7272]

I don't use 2007, but the code looks ok. If you hit Alt+F11 > Tools >
References...are there any missing references? You may need to set a
reference for Microsoft excel 10.0 Object Library.
Just a wild guess.

HTH,
Ryan---

 

[Robert]

Thanks, the Microsoft Excel 11.0 object library is checked...will that do?
R.

 

[Dave Peterson]

Just a guess, but I'd bet it was resources.

Do you know what sheet name you were processing (how far into the list)?

Maybe, you could try doing it twice:
first:
Set ListRng = .Range("A7:A30")
and second:
Set ListRng = .Range("A31:A51")

Just to see if that helps.

If it does, then change your code and include a comment to yourself how to
work-around the resource issue.