I agree with Tom and for that reason I avoid using the
copy command and the cut command whenever possible. I
instead try have the value in a cell or range = the value
in another cell or range.
example: thiscell.value = thatcell.value
or
thisrange.value = thatrange.value
you will have to name the target range prior to the
command and you may have to do some pre-formatting.
Not sure but that seems to cut down the problem.
-----Original 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



.