MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

run-time error 1004 "Copy method of Worksheet class Failed'

  Asked By: Doan    Date: Jan 11    Category: MS Office    Views: 4012

I have code to copy a master tab, rename the tab based on data then
update several cells inside the new tab. By the end of the macro
there will be well over 100 new tabs in the workbook. The loop works
for the first 40 or so, then crashes with Run-Time Error 1004.

When I close the macro and attempt to copy a sheet it acts like it
did it, but no new tab is created. I am able to insert new
worksheet. When I save, close, reopen I can manually copy more tabs.

I found on the internet that Microsoft is telling me to do what I
found on my own as a work around, but there has to be a better way
than this bandaide. Save/close/reopen will take too much cycle time
for the user. I tried saving alone to no avail. It has to be closed
and reopened.

Anyone else have a better aproach than Microsoft?



2 Answers Found

Answer #1    Answered By: Ginger Snyder     Answered On: Jan 11

I'm using Excel 2003, and I'm unable to produce the error  you mention.
I was able to add 100 new sheets. (I named Sheet1 - "Error"). I
was also able to copy  an existing worksheet  100 times. Maybe the
error is produced in the order of operations in your code. Changing
the order might avoid the error.

It might help to share the code you are using.

Answer #2    Answered By: Jimmy Abp     Answered On: Jan 11

The workaround given by MS says that the number you can do before an error
is dependent on the size of the sheet you are copying.