I have an Excel worksheet template with formats, formulas, etc.
I wrote code to automatically copy the template worksheet, then assign a
name to the new worksheet. The code references a list of 56 names to loop
through in creating 56 worksheets. However, after creating 16 worksheets, a
runtime error 1004 always occurs - Copy method of Worksheet Class failed.
After resetting the Sub Procedure I cannot create any more worksheets,
manually or with code, until the workbook is closed and reopened. Can this
problem be overcome so I don't have to create on worksheet at a time?
The code to create and name the new worksheets is as follows:

'extract the first Dealer name from the "Dealer_Names" range & then loop
through
'the list to create worksheets for each Dealer.
For j = 1 To Worksheets("LOOKUP").Range("Dealer_Names").Rows.Count Step 1
strN = Worksheets("LOOKUP").Range("Dealer_Names").Cells(j, 1)
strFMRGDlrN = Worksheets("LOOKUP").Range("Dealer_Names").Cells(j, 2)

'select the "Dealer_Template" worksheet, copy and insert it at the specified
location
Worksheets("Dealer_Template").Select
Worksheets("Dealer_Template").Copy Before:=Worksheets(Worksheets.Count -
5)

'give the worksheet template a new name
ActiveSheet.Name = strN
'determine cell address on the "Dealer_Template" in which to enter the
Dealer name
strDlrCell = Worksheets("LOOKUP").Range("Dlr_Name_Wksht_Cell").Cells(1, 2)
strFMRGDlrCell =
Worksheets("LOOKUP").Range("Dlr_Name_Wksht_Cell").Cells(2, 2)

'insert the Dealer lookup names in the correct cell addresses
With Worksheets(strN)
.Range(strDlrCell) = strN
.Range(strFMRGDlrCell) = strFMRGDlrN
.Activate
.Range("A1").Activate
End With
Next j

try saving the workbook after every 5 sheets are copied. Line added before
the next statement.

For j = 1 To Worksheets("LOOKUP").Range("Dealer_Names").Rows.Count Step 1
strN = Worksheets("LOOKUP").Range("Dealer_Names").Cells(j, 1)
strFMRGDlrN = Worksheets("LOOKUP").Range("Dealer_Names").Cells(j, 2)

'select the "Dealer_Template" worksheet, copy and insert it at the specified
location
Worksheets("Dealer_Template").Select
Worksheets("Dealer_Template").Copy Before:=Worksheets(Worksheets.Count -
5)

'give the worksheet template a new name
ActiveSheet.Name = strN
'determine cell address on the "Dealer_Template" in which to enter the
Dealer name
strDlrCell = Worksheets("LOOKUP").Range("Dlr_Name_Wksht_Cell").Cells(1, 2)
strFMRGDlrCell =
Worksheets("LOOKUP").Range("Dlr_Name_Wksht_Cell").Cells(2, 2)

'insert the Dealer lookup names in the correct cell addresses
With Worksheets(strN)
.Range(strDlrCell) = strN
.Range(strFMRGDlrCell) = strFMRGDlrN
.Activate
.Range("A1").Activate
End With

if j mod 5 = 0 then _
ActiveWorkbook.Save

Next j

--
Regards,
Tom Ogilvy


"WilliamI" wrote:

> I have an Excel worksheet template with formats, formulas, etc.
> I wrote code to automatically copy the template worksheet, then assign a
> name to the new worksheet. The code references a list of 56 names to loop
> through in creating 56 worksheets. However, after creating 16 worksheets, a
> runtime error 1004 always occurs - Copy method of Worksheet Class failed.
> After resetting the Sub Procedure I cannot create any more worksheets,
> manually or with code, until the workbook is closed and reopened. Can this
> problem be overcome so I don't have to create on worksheet at a time?
> The code to create and name the new worksheets is as follows:
>
> 'extract the first Dealer name from the "Dealer_Names" range & then loop
> through
> 'the list to create worksheets for each Dealer.
> For j = 1 To Worksheets("LOOKUP").Range("Dealer_Names").Rows.Count Step 1
> strN = Worksheets("LOOKUP").Range("Dealer_Names").Cells(j, 1)
> strFMRGDlrN = Worksheets("LOOKUP").Range("Dealer_Names").Cells(j, 2)
>
> 'select the "Dealer_Template" worksheet, copy and insert it at the specified
> location
> Worksheets("Dealer_Template").Select
> Worksheets("Dealer_Template").Copy Before:=Worksheets(Worksheets.Count -
> 5)
>
> 'give the worksheet template a new name
> ActiveSheet.Name = strN
> 'determine cell address on the "Dealer_Template" in which to enter the
> Dealer name
> strDlrCell = Worksheets("LOOKUP").Range("Dlr_Name_Wksht_Cell").Cells(1, 2)
> strFMRGDlrCell =
> Worksheets("LOOKUP").Range("Dlr_Name_Wksht_Cell").Cells(2, 2)
>
> 'insert the Dealer lookup names in the correct cell addresses
> With Worksheets(strN)
> .Range(strDlrCell) = strN
> .Range(strFMRGDlrCell) = strFMRGDlrN
> .Activate
> .Range("A1").Activate
> End With
> Next j

I tried your solution, which made sense. However, after creating 16
worksheets the error still occurs.

"Tom Ogilvy" wrote:

> try saving the workbook after every 5 sheets are copied. Line added before
> the next statement.
>
> For j = 1 To Worksheets("LOOKUP").Range("Dealer_Names").Rows.Count Step 1
> strN = Worksheets("LOOKUP").Range("Dealer_Names").Cells(j, 1)
> strFMRGDlrN = Worksheets("LOOKUP").Range("Dealer_Names").Cells(j, 2)
>
> 'select the "Dealer_Template" worksheet, copy and insert it at the specified
> location
> Worksheets("Dealer_Template").Select
> Worksheets("Dealer_Template").Copy Before:=Worksheets(Worksheets.Count -
> 5)
>
> 'give the worksheet template a new name
> ActiveSheet.Name = strN
> 'determine cell address on the "Dealer_Template" in which to enter the
> Dealer name
> strDlrCell = Worksheets("LOOKUP").Range("Dlr_Name_Wksht_Cell").Cells(1, 2)
> strFMRGDlrCell =
> Worksheets("LOOKUP").Range("Dlr_Name_Wksht_Cell").Cells(2, 2)
>
> 'insert the Dealer lookup names in the correct cell addresses
> With Worksheets(strN)
> .Range(strDlrCell) = strN
> .Range(strFMRGDlrCell) = strFMRGDlrN
> .Activate
> .Range("A1").Activate
> End With
>
> if j mod 5 = 0 then _
> ActiveWorkbook.Save
>
> Next j
>
> --
> Regards,
> Tom Ogilvy
>
>
> "WilliamI" wrote:
>
> > I have an Excel worksheet template with formats, formulas, etc.
> > I wrote code to automatically copy the template worksheet, then assign a
> > name to the new worksheet. The code references a list of 56 names to loop
> > through in creating 56 worksheets. However, after creating 16 worksheets, a
> > runtime error 1004 always occurs - Copy method of Worksheet Class failed.
> > After resetting the Sub Procedure I cannot create any more worksheets,
> > manually or with code, until the workbook is closed and reopened. Can this
> > problem be overcome so I don't have to create on worksheet at a time?
> > The code to create and name the new worksheets is as follows:
> >
> > 'extract the first Dealer name from the "Dealer_Names" range & then loop
> > through
> > 'the list to create worksheets for each Dealer.
> > For j = 1 To Worksheets("LOOKUP").Range("Dealer_Names").Rows.Count Step 1
> > strN = Worksheets("LOOKUP").Range("Dealer_Names").Cells(j, 1)
> > strFMRGDlrN = Worksheets("LOOKUP").Range("Dealer_Names").Cells(j, 2)
> >
> > 'select the "Dealer_Template" worksheet, copy and insert it at the specified
> > location
> > Worksheets("Dealer_Template").Select
> > Worksheets("Dealer_Template").Copy Before:=Worksheets(Worksheets.Count -
> > 5)
> >
> > 'give the worksheet template a new name
> > ActiveSheet.Name = strN
> > 'determine cell address on the "Dealer_Template" in which to enter the
> > Dealer name
> > strDlrCell = Worksheets("LOOKUP").Range("Dlr_Name_Wksht_Cell").Cells(1, 2)
> > strFMRGDlrCell =
> > Worksheets("LOOKUP").Range("Dlr_Name_Wksht_Cell").Cells(2, 2)
> >
> > 'insert the Dealer lookup names in the correct cell addresses
> > With Worksheets(strN)
> > .Range(strDlrCell) = strN
> > .Range(strFMRGDlrCell) = strFMRGDlrN
> > .Activate
> > .Range("A1").Activate
> > End With
> > Next j

I would try the same code or similar code in a new workbook with blank sheets.

If that works fine, then move the template sheet into that workbook and try
it again but this time, copying the template sheet.


I have seen it said that the number of copies is more a problem than the
number of sheets created. by that I mean, that if you made 5 template
sheets, then copied them 5 at a time, you might get much farther than you are
now.

going back to the text, if the second test (copying the template) works,
then I would do

worksheets("template").copy ' copies it to a new workbook)
now create 56 or whatever copies you need in the new workbook and process
them. then copy them back all at once. Close the new workbook without
saving.

--
Regards,
Tom Ogilvy


"WilliamI" wrote:

> I tried your solution, which made sense. However, after creating 16
> worksheets the error still occurs.
>
> "Tom Ogilvy" wrote:
>
> > try saving the workbook after every 5 sheets are copied. Line added before
> > the next statement.
> >
> > For j = 1 To Worksheets("LOOKUP").Range("Dealer_Names").Rows.Count Step 1
> > strN = Worksheets("LOOKUP").Range("Dealer_Names").Cells(j, 1)
> > strFMRGDlrN = Worksheets("LOOKUP").Range("Dealer_Names").Cells(j, 2)
> >
> > 'select the "Dealer_Template" worksheet, copy and insert it at the specified
> > location
> > Worksheets("Dealer_Template").Select
> > Worksheets("Dealer_Template").Copy Before:=Worksheets(Worksheets.Count -
> > 5)
> >
> > 'give the worksheet template a new name
> > ActiveSheet.Name = strN
> > 'determine cell address on the "Dealer_Template" in which to enter the
> > Dealer name
> > strDlrCell = Worksheets("LOOKUP").Range("Dlr_Name_Wksht_Cell").Cells(1, 2)
> > strFMRGDlrCell =
> > Worksheets("LOOKUP").Range("Dlr_Name_Wksht_Cell").Cells(2, 2)
> >
> > 'insert the Dealer lookup names in the correct cell addresses
> > With Worksheets(strN)
> > .Range(strDlrCell) = strN
> > .Range(strFMRGDlrCell) = strFMRGDlrN
> > .Activate
> > .Range("A1").Activate
> > End With
> >
> > if j mod 5 = 0 then _
> > ActiveWorkbook.Save
> >
> > Next j
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "WilliamI" wrote:
> >
> > > I have an Excel worksheet template with formats, formulas, etc.
> > > I wrote code to automatically copy the template worksheet, then assign a
> > > name to the new worksheet. The code references a list of 56 names to loop
> > > through in creating 56 worksheets. However, after creating 16 worksheets, a
> > > runtime error 1004 always occurs - Copy method of Worksheet Class failed.
> > > After resetting the Sub Procedure I cannot create any more worksheets,
> > > manually or with code, until the workbook is closed and reopened. Can this
> > > problem be overcome so I don't have to create on worksheet at a time?
> > > The code to create and name the new worksheets is as follows:
> > >
> > > 'extract the first Dealer name from the "Dealer_Names" range & then loop
> > > through
> > > 'the list to create worksheets for each Dealer.
> > > For j = 1 To Worksheets("LOOKUP").Range("Dealer_Names").Rows.Count Step 1
> > > strN = Worksheets("LOOKUP").Range("Dealer_Names").Cells(j, 1)
> > > strFMRGDlrN = Worksheets("LOOKUP").Range("Dealer_Names").Cells(j, 2)
> > >
> > > 'select the "Dealer_Template" worksheet, copy and insert it at the specified
> > > location
> > > Worksheets("Dealer_Template").Select
> > > Worksheets("Dealer_Template").Copy Before:=Worksheets(Worksheets.Count -
> > > 5)
> > >
> > > 'give the worksheet template a new name
> > > ActiveSheet.Name = strN
> > > 'determine cell address on the "Dealer_Template" in which to enter the
> > > Dealer name
> > > strDlrCell = Worksheets("LOOKUP").Range("Dlr_Name_Wksht_Cell").Cells(1, 2)
> > > strFMRGDlrCell =
> > > Worksheets("LOOKUP").Range("Dlr_Name_Wksht_Cell").Cells(2, 2)
> > >
> > > 'insert the Dealer lookup names in the correct cell addresses
> > > With Worksheets(strN)
> > > .Range(strDlrCell) = strN
> > > .Range(strFMRGDlrCell) = strFMRGDlrN
> > > .Activate
> > > .Range("A1").Activate
> > > End With
> > > Next j

I tried what you suggested in a new workbook and the code created all 56
worksheets. The problem may be with the template worksheet that uses a lot
VLookup, Index and Match functions to extract data from common data source
worksheets containing sales & financial data for all Dealers. However, making
calculation manual or automatic didn't make any difference. At least I know
the problem is not with the code. Thanks for your help.

"Tom Ogilvy" wrote:

> I would try the same code or similar code in a new workbook with blank sheets.
>
> If that works fine, then move the template sheet into that workbook and try
> it again but this time, copying the template sheet.
>
>
> I have seen it said that the number of copies is more a problem than the
> number of sheets created. by that I mean, that if you made 5 template
> sheets, then copied them 5 at a time, you might get much farther than you are
> now.
>
> going back to the text, if the second test (copying the template) works,
> then I would do
>
> worksheets("template").copy ' copies it to a new workbook)
> now create 56 or whatever copies you need in the new workbook and process
> them. then copy them back all at once. Close the new workbook without
> saving.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "WilliamI" wrote:
>
> > I tried your solution, which made sense. However, after creating 16
> > worksheets the error still occurs.
> >
> > "Tom Ogilvy" wrote:
> >
> > > try saving the workbook after every 5 sheets are copied. Line added before
> > > the next statement.
> > >
> > > For j = 1 To Worksheets("LOOKUP").Range("Dealer_Names").Rows.Count Step 1
> > > strN = Worksheets("LOOKUP").Range("Dealer_Names").Cells(j, 1)
> > > strFMRGDlrN = Worksheets("LOOKUP").Range("Dealer_Names").Cells(j, 2)
> > >
> > > 'select the "Dealer_Template" worksheet, copy and insert it at the specified
> > > location
> > > Worksheets("Dealer_Template").Select
> > > Worksheets("Dealer_Template").Copy Before:=Worksheets(Worksheets.Count -
> > > 5)
> > >
> > > 'give the worksheet template a new name
> > > ActiveSheet.Name = strN
> > > 'determine cell address on the "Dealer_Template" in which to enter the
> > > Dealer name
> > > strDlrCell = Worksheets("LOOKUP").Range("Dlr_Name_Wksht_Cell").Cells(1, 2)
> > > strFMRGDlrCell =
> > > Worksheets("LOOKUP").Range("Dlr_Name_Wksht_Cell").Cells(2, 2)
> > >
> > > 'insert the Dealer lookup names in the correct cell addresses
> > > With Worksheets(strN)
> > > .Range(strDlrCell) = strN
> > > .Range(strFMRGDlrCell) = strFMRGDlrN
> > > .Activate
> > > .Range("A1").Activate
> > > End With
> > >
> > > if j mod 5 = 0 then _
> > > ActiveWorkbook.Save
> > >
> > > Next j
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > >
> > > "WilliamI" wrote:
> > >
> > > > I have an Excel worksheet template with formats, formulas, etc.
> > > > I wrote code to automatically copy the template worksheet, then assign a
> > > > name to the new worksheet. The code references a list of 56 names to loop
> > > > through in creating 56 worksheets. However, after creating 16 worksheets, a
> > > > runtime error 1004 always occurs - Copy method of Worksheet Class failed.
> > > > After resetting the Sub Procedure I cannot create any more worksheets,
> > > > manually or with code, until the workbook is closed and reopened. Can this
> > > > problem be overcome so I don't have to create on worksheet at a time?
> > > > The code to create and name the new worksheets is as follows:
> > > >
> > > > 'extract the first Dealer name from the "Dealer_Names" range & then loop
> > > > through
> > > > 'the list to create worksheets for each Dealer.
> > > > For j = 1 To Worksheets("LOOKUP").Range("Dealer_Names").Rows.Count Step 1
> > > > strN = Worksheets("LOOKUP").Range("Dealer_Names").Cells(j, 1)
> > > > strFMRGDlrN = Worksheets("LOOKUP").Range("Dealer_Names").Cells(j, 2)
> > > >
> > > > 'select the "Dealer_Template" worksheet, copy and insert it at the specified
> > > > location
> > > > Worksheets("Dealer_Template").Select
> > > > Worksheets("Dealer_Template").Copy Before:=Worksheets(Worksheets.Count -
> > > > 5)
> > > >
> > > > 'give the worksheet template a new name
> > > > ActiveSheet.Name = strN
> > > > 'determine cell address on the "Dealer_Template" in which to enter the
> > > > Dealer name
> > > > strDlrCell = Worksheets("LOOKUP").Range("Dlr_Name_Wksht_Cell").Cells(1, 2)
> > > > strFMRGDlrCell =
> > > > Worksheets("LOOKUP").Range("Dlr_Name_Wksht_Cell").Cells(2, 2)
> > > >
> > > > 'insert the Dealer lookup names in the correct cell addresses
> > > > With Worksheets(strN)
> > > > .Range(strDlrCell) = strN
> > > > .Range(strFMRGDlrCell) = strFMRGDlrN
> > > > .Activate
> > > > .Range("A1").Activate
> > > > End With
> > > > Next j

Here is something else you might try:

You might run code on the template the replaces all formulas with a prefix
so they are simple text strings (get the code from doing the action with the
macro recorder turned on; Edit=>Replace)

replace "="
replacment "XXX="

copy the sheet 56 times or using a grouped strategy

go to each sheet and change them back.

--
Regards,
Tom Ogilvy


"WilliamI" <(E-Mail Removed)> wrote in message
news:69D9B630-8A24-4D25-8152-(E-Mail Removed)...
>I tried what you suggested in a new workbook and the code created all 56
> worksheets. The problem may be with the template worksheet that uses a lot
> VLookup, Index and Match functions to extract data from common data source
> worksheets containing sales & financial data for all Dealers. However,
> making
> calculation manual or automatic didn't make any difference. At least I
> know
> the problem is not with the code. Thanks for your help.
>
> "Tom Ogilvy" wrote:
>
>> I would try the same code or similar code in a new workbook with blank
>> sheets.
>>
>> If that works fine, then move the template sheet into that workbook and
>> try
>> it again but this time, copying the template sheet.
>>
>>
>> I have seen it said that the number of copies is more a problem than the
>> number of sheets created. by that I mean, that if you made 5 template
>> sheets, then copied them 5 at a time, you might get much farther than you
>> are
>> now.
>>
>> going back to the text, if the second test (copying the template) works,
>> then I would do
>>
>> worksheets("template").copy ' copies it to a new workbook)
>> now create 56 or whatever copies you need in the new workbook and process
>> them. then copy them back all at once. Close the new workbook without
>> saving.
>>
>> --
>> Regards,
>> Tom Ogilvy
>>
>>
>> "WilliamI" wrote:
>>
>> > I tried your solution, which made sense. However, after creating 16
>> > worksheets the error still occurs.
>> >
>> > "Tom Ogilvy" wrote:
>> >
>> > > try saving the workbook after every 5 sheets are copied. Line added
>> > > before
>> > > the next statement.
>> > >
>> > > For j = 1 To Worksheets("LOOKUP").Range("Dealer_Names").Rows.Count
>> > > Step 1
>> > > strN = Worksheets("LOOKUP").Range("Dealer_Names").Cells(j, 1)
>> > > strFMRGDlrN = Worksheets("LOOKUP").Range("Dealer_Names").Cells(j,
>> > > 2)
>> > >
>> > > 'select the "Dealer_Template" worksheet, copy and insert it at the
>> > > specified
>> > > location
>> > > Worksheets("Dealer_Template").Select
>> > > Worksheets("Dealer_Template").Copy
>> > > Before:=Worksheets(Worksheets.Count -
>> > > 5)
>> > >
>> > > 'give the worksheet template a new name
>> > > ActiveSheet.Name = strN
>> > > 'determine cell address on the "Dealer_Template" in which to enter
>> > > the
>> > > Dealer name
>> > > strDlrCell =
>> > > Worksheets("LOOKUP").Range("Dlr_Name_Wksht_Cell").Cells(1, 2)
>> > > strFMRGDlrCell =
>> > > Worksheets("LOOKUP").Range("Dlr_Name_Wksht_Cell").Cells(2, 2)
>> > >
>> > > 'insert the Dealer lookup names in the correct cell addresses
>> > > With Worksheets(strN)
>> > > .Range(strDlrCell) = strN
>> > > .Range(strFMRGDlrCell) = strFMRGDlrN
>> > > .Activate
>> > > .Range("A1").Activate
>> > > End With
>> > >
>> > > if j mod 5 = 0 then _
>> > > ActiveWorkbook.Save
>> > >
>> > > Next j
>> > >
>> > > --
>> > > Regards,
>> > > Tom Ogilvy
>> > >
>> > >
>> > > "WilliamI" wrote:
>> > >
>> > > > I have an Excel worksheet template with formats, formulas, etc.
>> > > > I wrote code to automatically copy the template worksheet, then
>> > > > assign a
>> > > > name to the new worksheet. The code references a list of 56 names
>> > > > to loop
>> > > > through in creating 56 worksheets. However, after creating 16
>> > > > worksheets, a
>> > > > runtime error 1004 always occurs - Copy method of Worksheet Class
>> > > > failed.
>> > > > After resetting the Sub Procedure I cannot create any more
>> > > > worksheets,
>> > > > manually or with code, until the workbook is closed and reopened.
>> > > > Can this
>> > > > problem be overcome so I don't have to create on worksheet at a
>> > > > time?
>> > > > The code to create and name the new worksheets is as follows:
>> > > >
>> > > > 'extract the first Dealer name from the "Dealer_Names" range & then
>> > > > loop
>> > > > through
>> > > > 'the list to create worksheets for each Dealer.
>> > > > For j = 1 To Worksheets("LOOKUP").Range("Dealer_Names").Rows.Count
>> > > > Step 1
>> > > > strN = Worksheets("LOOKUP").Range("Dealer_Names").Cells(j, 1)
>> > > > strFMRGDlrN =
>> > > > Worksheets("LOOKUP").Range("Dealer_Names").Cells(j, 2)
>> > > >
>> > > > 'select the "Dealer_Template" worksheet, copy and insert it at the
>> > > > specified
>> > > > location
>> > > > Worksheets("Dealer_Template").Select
>> > > > Worksheets("Dealer_Template").Copy
>> > > > Before:=Worksheets(Worksheets.Count -
>> > > > 5)
>> > > >
>> > > > 'give the worksheet template a new name
>> > > > ActiveSheet.Name = strN
>> > > > 'determine cell address on the "Dealer_Template" in which to enter
>> > > > the
>> > > > Dealer name
>> > > > strDlrCell =
>> > > > Worksheets("LOOKUP").Range("Dlr_Name_Wksht_Cell").Cells(1, 2)
>> > > > strFMRGDlrCell =
>> > > > Worksheets("LOOKUP").Range("Dlr_Name_Wksht_Cell").Cells(2, 2)
>> > > >
>> > > > 'insert the Dealer lookup names in the correct cell addresses
>> > > > With Worksheets(strN)
>> > > > .Range(strDlrCell) = strN
>> > > > .Range(strFMRGDlrCell) = strFMRGDlrN
>> > > > .Activate
>> > > > .Range("A1").Activate
>> > > > End With
>> > > > Next j