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