In addition to the other suggestions, you might want to review your "list of
names". Is there something different about the 30th or 31st entry that might
be causing a problem (i.e., a space in the name that the previous entries
didn't have?, is it longer than the previous entries? more than 255
characters?)

My point is that it may not be the copying that is breaking but the
creation/naming of the new workbook.

--
George Nicholson

Remove 'Junk' from return address.

"Alex" wrote in message
...
Hi,

Can anyone please workout why I'm getting a Run-Time Error 1004 (Copy
method of worksheet class failed) in code after 30 sheets have been copied?
Code works fine up until then. Is there a limit on how many sheets can be
copied to new workbook. This is confusing because if your open a new
file, it can have up to 255 sheets. All my code is doing is running through
a list of names and for each copying a template sheet into a new workbook.
I have 50 names which I need to create the same template sheet for each name
and store in one new workbook. For some reason can't get past 30 sheets.

Appreciate any help with this.
Alex

XL2003 VBA


' loop
While (Ctr) < EmpCtr
CrtEmpName = Workbooks(DataFile).Sheets(DataSht).Cells(rCtr,
1).Value ' Master file containing employee names sheet

' copy template to new workbook on first occurance
If ShtCtr = 0 Then
Sheets(CalcSht).Copy ' Template sheet to copy
ActiveWorkbook.Sheets(CalcSht).Activate ' New workbook
created with tempate sheet copied
CalcFile = ActiveWorkbook.Name '
Else
R-T Error -- Sheets(CalcSht).Copy
After:=Workbooks(CalcFile).Sheets(ShtCtr)

End If
ActiveSheet.Name = CrtEmpName

Workbooks(DataFile).Sheets(CalcSht).Activate ' Master file
containing template sheet
rCtr = rCtr + 1
Ctr = Ctr + 1
ShtCtr = ShtCtr + 1
Wend