Run time Error 1004: Copy method of Worksheet Class failed

TREY

New Member
Joined
Apr 11, 2002
Messages
3
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.
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Ivan F Moala

MrExcel MVP
Joined
Feb 10, 2002
Messages
4,209
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

New Member
Joined
Apr 11, 2002
Messages
3
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
 

Watch MrExcel Video

Forum statistics

Threads
1,101,894
Messages
5,483,550
Members
407,397
Latest member
HerbA

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top