MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Copy method of worksheet class failed ?

Posted by Lieuwer on August 06, 2001 2:24 AM

Ones in a while I don't manage to copy a worksheet within a workbook using Edit / Copy or move a sheet. The new sheet simply does not appear and this without any worning.
The same happens in a macro but then I get the message: Copy method of worksheet class failed. Error 1004.
Does anybody know why this happens and what to do about it???


Posted by Cory on August 06, 2001 8:08 AM

The only things I can think of that may cause this are:

1. Your system's too low on memory to do this task
2. There's some invalid reference in your macro code keeping it from copying, or
3. Sometimes after using an application (such as excel) for a long time, your registry data will be updated to reflect certain settings made from customizations (adding toolbar components, changing options, etc...). On occasion, after enough registry keys havebeen altered (updated), some other functions won't work. The easiest way to fix this (unfortunately) is to restore a backup from the last known time the failing function worked, or to reinstall Excel itself.

If it's not any of that, I'm stumped...


Posted by Lieuwer on August 06, 2001 6:30 PM

Dear Cory,
Excuse my ignorance but two questions.
What are these registry keys.
And what do you mean with "is to restore a backup from the last known time the failing function worked" ? Is this the excelprogram I made or something else.

I must admit that I was playing a Chinese CD at the time and their crapy technical quality is a heavy demand on the system (Celeron 400 128 Mb), but as far as I remembered it was as well the case on my desktop (PIII/450, 64 Kb) without any music playing. Could this effect the Excel performance?

The strange thing is that it does not give a warning that the fuction could not be performed.

It is not in the code. Most of the time it works fine. And it does the same thing while manually doing this function.


Posted by Cory on August 06, 2001 7:39 PM

Don't be sorry for your ignorance when you aren't ignorant at all. I should apologize about being so unclear.

When I said there may be an invalid reference in our code, I didn't simply mean there might be incorrect syntax, I also meant there may just be something out of order such as trying to paste somewhere it's not allowed to paste...

The registry keys are deep in your operating system (if you use Windows 95, 98, nt, 2000...) and they hold the settings for everything you see and run on your computer. From the way your windows look to the way excel starts when you click the icon. It holds what printers are connected, what kind of mouse you use, what color font you're using at the time, etc...

Whenever you change a setting in excel, like something in the Tools --> Options menu, you change a "key" in the regisrty. A key is a file that contains the settings for each part of your computer and the software thats on it. After a year or two of doing this, some keys are changed so much from what they originally were, they just stop working. Sometimes when you try and run simple applications, like MSPaint or MSImaging, you'll get a "SoAndSO.dll file missing or corrupted. Please specify location."

It comes in many forms, but many times it's nothing major. Like the .dll thing above can be fixed by getting a good copy of the file from someone else's computer and copying it onto yours. Problem solved...

Running anything while running Excel, such as the CD player you mentioned, Could slow it down some, but nothing noticeable on your system. I can't think of much to solve your problem except:
run disk cleanup
and registry checker

if you haven't already.

And about the backup: if you have a backup copy from before you saw this problem start, you could open it and compare it's code to your current copy to see if you can find out what's causing the problem..

Hope that was a little better. Don't worry about the registry too much, but do run the system maintenance stuff and compare the files if you can...


Posted by Lieuwer on August 07, 2001 10:15 PM

Dear Cory,

The problem happened twice to me and if I take an earlier version then the problem is gone. I am afraid that it will come back after distribution of the application, so I like to understand it.

On both computers I run reasently installed copies less then a month old and I don't change the registry to much apart from a start up macro who sets excel to one sheet per workbook.

could the workbook size be a couse. At the moment it is 4500Kb?