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

Lima Mike

Board Regular
Joined
Jun 3, 2003
Messages
70
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
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
Can you see what the Excel internal name for the final copied sheets is ( Project Explorer in VBE should let you see )?
 

Lima Mike

Board Regular
Joined
Jun 3, 2003
Messages
70
The name of the sheet is Sheet 80.
(The thing crashes after 80 copies on the 81 copy)

Lima Mike
 

Forum statistics

Threads
1,141,069
Messages
5,704,105
Members
421,327
Latest member
Msh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top