Copy method of Worksheet class failed. WHY????

[Lima Mike]

Dear All,

I have problems while running a macro that copies a page from one workbook to an other.
All goes well but after some 80 copies the thing crashes with the following message:

Run-time error ‘1004’ :
Copy method of Worksheet class failed.

The line on which it crashes is:

Sheets("PAYSLIP MODEL").Copy Before:=Workbooks(theFileName).Sheets(1)

What I am doing is copying a sheet with data that is linked via cell references to an other sheet in the same workbook to an other workbook while I copy and paste back the values. (Links shows up but in fact there are none)

Not only the macro is unable to copy the sheet to the new workbook after 80 times doing so but it is as well impossible to do so by hand. (It is possible to insert new sheets while being in the workbook with the 80 sheets)

What is causing this phenomenon and how to get over it?

Lima Mike

 

[GlennUK]

Can you see what the Excel internal name for the final copied sheets is ( Project Explorer in VBE should let you see )?

 

[Lima Mike]

The name of the sheet is Sheet 80.
(The thing crashes after 80 copies on the 81 copy)

Lima Mike