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

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---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Robert" wrote:

> 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
>

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

"ryguy7272" wrote:

> 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---
>
> --
> Ryan---
> If this information was helpful, please indicate this by clicking ''Yes''.
>
>
> "Robert" wrote:
>
> > 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
> >

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.



Robert wrote:
>
> 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

--

Dave Peterson