Run-Time Error 1004 Copy method of worksheet class failed
 

Alex,

Just an initial observation for starters:
If the names in brackets following Workbooks and Sheets are their respective
names then they should all be enclosed in quotation marks. e.g Workbooks(
"DataFile" ).Sheets( "DataSht" ).Cells(rCtr, 1).Value

regards,
Don

"Alex." wrote in message
...
Hi,

Can anyone please workout why I'm getting a run time error 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