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.Co unt 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 Befo=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.Co unt 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 Befo=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.Co unt 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 Befo=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.Co unt 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 Befo=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.Co unt 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 Befo=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.Co unt 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 Befo=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.Co unt 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 Befo=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.Co unt 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 Befo=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.Co unt 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 Befo=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" wrote in message
...
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.Co unt
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
Befo=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.Co unt
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
Befo=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