Run time Error 1004: Copy method of Worksheet Class failed

[TREY]

I keep getting this error when creating a number of copies of a worksheet. I have tried: turning off the AutoCalculation, Copying a less complex worksheet, Insterting Do events - And have had now success. I was wondering would inserting a timer to slow down the copying help. Any help would be appreciated.

[Ivan F Moala]

On 2002-04-12 01:44, TREY wrote:
I keep getting this error when creating a number of copies of a worksheet. I have tried: turning off the AutoCalculation, Copying a less complex worksheet, Insterting Do events - And have had now success. I was wondering would inserting a timer to slow down the copying help. Any help would be appreciated.

Hi Trey

Just a couple of Q's
1) What version of Excel
2) How are you calling the routine
eg commandbutton,
3) What code are you using

[TREY]

Thanks for replying:
(1) I am using Excel 97 but the same problem appears in Excel 2000
(2)I am using a command button in another sheet to start the procedure which: Deletes all worksheets in the file except for a specific 3. Then it creates the same number of copies of a timekeeping worksheets as names that have been entered in a particular range. It names each worksheet with its corresponding name in the range. The worksheet that is being copied has many drop down boxes but I still get the same problem is I copy a basic worksheet. Granted the error occurs after many more worksheets have been copied with the simpler worksheet. It looks like a memory problem as when the woorkbook is closed the macro will run once without errors when it is reopened, but even this is not hard and fast experience. When I looked up the net it pointed that the problem may be due to excel trying to do calculations but I turned on manual calculation and still encountered the problem.
(3)Here is the code where the problem is, I can send you on the rest of the code if it is any help- as it is quite short.

Do Until Timesheet_number = Counter

Sheets("Timesheet").Select
Sheets("Timesheet").copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
Timesheet_number = Timesheet_number + 1
Loop


Thanks

[Ivan F Moala]

hi Trey

possible to email me full code and structure of SS?

[TREY]

The solution to my problem can be found at the following 2 web sites, I hope it saves somone else time, thanks Ivan.

http://support.microsoft.com/default...;en-us;Q210684


http://support.microsoft.com/default...;en-us;Q177634