"Runtime Error 1004: Copy method of worksheet class failed " in VBA on first copy attempt

Question

(Sorry in advance for my poor English: not first Language :) ) I am writing a VBA Excel 2003 routine that runs through 50+ worksheets in a file, copies the worksheets (just as a temp copy) one by one and then performs actions on them before removing these temporary copies and returning the result of it's calculations on the worksheets content. To be more precise: the code is called from an external file with a single (hidden) worksheet inside. When I open the file it runs a code to create a new toolbar in Excel, when I press a button on the toolbar, the code I have described above runs.

I know that not saving a file and performing many and many copies triggers this error, but now it is triggering at the first attempt (I have closed and reopened everything multiple times to be sure I am not keeping the not saved situation with me).

This is the code triggering the problem, I am sorry for poor formatting:

ActiveWorkbook.Worksheets("NAME OF THE FIRST WORKSHEET I WANT TO COPY").Copy ThisWorkbook.Worksheets("HiddenSheet")

Disclamer: the name of the worksheet is found by a For..Next cycle through the ActiveWorkbook.Worksheets array, but the code is not working even if i hard-code the name myself.

Here is a larger chunk of the code, to be clearer:

Set sourceWorkbook = ActiveWorkbook
For index = 1 To sourceWorkbook.Worksheets.Count
    sourceWorkbook.Activate 'not sure if this is even needed
    Set currWorksheet = sourceWorkbook.Worksheets(index)
    currWorksheet.Copy ThisWorkbook.Worksheets("HiddenSheet")
Next index

The result is now consistently:

Run-time Error '1004' Copy method of worksheet class failed.

I thank everybody in advance for the help!

Are you trying to copy the Worksheet or the contents of the Worksheet? The syntax is slightly different for both and you seem to have mixed them up slightly. — , Jun 20 '19 at 13:32
Well, This can be caused by a couple of things, First off make sure the worksheet you're trying to copy actually exists. Also if you are sure it does. make sure it exists in the current ActiveWorkbook. The activeworkbook might change depending on what code is around it. Make sure to not use `.Select` and `.Activate` to a bit more sure of what is the current Workbook. See here [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). It might also be that HiddenSheet doesn't exist. Check that that one exists too. — erazorv4, Jun 20 '19 at 13:33
@erazorv4 if the worksheet didn't exist, you'd expect an Index Out of Range error *before* the chained call to `Copy` -- `ActiveWorkbook.Worksheets("sheet name")` would fail. So the sheet must exist. But something is disallowing copying. — David Zemens, Jun 20 '19 at 13:35
@DavidZemens Thought I saw this error on a problem like that somewhere. Must have been my imagination. — erazorv4, Jun 20 '19 at 13:36
The destination workbook exists as well as the source sheet and the destination sheet, also, nothing is protected. Moreover, the 2 workbooks are correctly referenced as for what i can see. By this I mean that i've used MsgBox to debug both ActiveWorkbook and ThisWorkbook verifying that the in fact contain the sheets they are supposed to contain — John Doe, Jun 20 '19 at 13:37
Just before the line above add `Debug.Print ActiveWorkbook.Name` and see what it prints. — , Jun 20 '19 at 13:40
Unrelated to your problem, but what do you mean by "destination sheet"? [`Worksheet.Copy`](https://learn.microsoft.com/en-us/office/vba/api/excel.worksheet.copy) doesn't copy to a destination sheet, it just copies to another location. It creates a copy of the sheet to be copied, and does not copy the sheet's contents to a specified destination sheet. — BigBen, Jun 20 '19 at 13:40
no protection that i know of (and i'm the one who created the file in the first place) by destination sheet i mean the "after" parameter of the copy… the sheet next to which the copy should be made — John Doe, Jun 20 '19 at 13:41
@BigBen is correct. There is no "destination" for the `Copy` method. You're using the `Before` parameter, so it's attempting to paste your copied worksheet *before* an existing sheet named `"Hidden Sheet"` in `ThisWorkbook`. — David Zemens, Jun 20 '19 at 13:42
where does Debug.Print actually print? i dont see anything appearing during execution of that line? @DavidZemens yes, exactly, i'm sorry if destination sheet is such a poor name choice, but i have no idea of how to reference it talking about it — John Doe, Jun 20 '19 at 13:44
The [Immediate Window](https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/use-the-immediate-window). Hopefully there is a similar documentation page in your native language. — BigBen, Jun 20 '19 at 13:45
@JohnDoe it prints in the *Immediate* pane of the VBE. If not visible in the VBE, it's under the View menu, or ctrl+G — David Zemens, Jun 20 '19 at 13:45
Debug.Print run… the workbook displayed it the right one... also, done the same for ThisWorkbook and that's right too — John Doe, Jun 20 '19 at 13:45
Remove `ThisWorkbook.Worksheets("HiddenSheet")` from your `Copy` statement. If you remove that, it should simply create a new workbook with the copied sheet. Let's see if that works, first. — David Zemens, Jun 20 '19 at 14:07
ALready tried it an it worked… it just doesnt make my case for the other 50+ sheets that have to go in the same place, i think(?) — John Doe, Jun 20 '19 at 14:08
I've expanded the provided code snipped, if it can help, have a look at my original question :) — John Doe, Jun 20 '19 at 14:11
Right, I'm not proposing that as a solution, I just wanted to isolate which side of that statement was the problem. If `Copy` successfully copies a sheet to a new workbook, then there's not a problem with the source worksheet. But something fishy about the destination. — David Zemens, Jun 20 '19 at 14:40
BIG UPDATE: ```SourceWorkbook.Worksheets.Copy``` doesn't fail, so the problem only lays in coying single files… so for this particular case I might manage to get my job done, even tough the bug still remains for single copies!!! — John Doe, Jun 21 '19 at 08:21

Error 1004 · Answer 1 · 2019-06-20T13:55:50.527

0

Some useful guidelines:

Option Explicit
'Copy sheet
Sub CopySheet()

    Dim ws1 As Workbook, ws2 As Workbook

    'It's better to declare sheets and avoid activate
    Set ws1 = Workbooks("Book1")
    Set ws2 = Workbooks("Book2")

    'Copy sheet "Test" from ws1(Book1) to ws2 (Book2) after all sheets
    ws1.Worksheets("Test").Copy After:=ws2.Worksheets(Sheets.Count)

End Sub

Option Explicit
'Copy a range
Sub CopyRange()

    Dim ws1 As Workbook, ws2 As Workbook

    'It's better to declare sheets and avoid activate
    Set ws1 = Workbooks("Book1")
    Set ws2 = Workbooks("Book2")

    'Copy from ws1(Book1), sheet "Test" & range A1:A5 to ws2 (Book2), sheet "sheet1" & range A1
    ws1.Worksheets("Test").Range("A1:A5").Copy
    ws2.Worksheets("Sheet1").Range("A1").PasteSpecial Paste:=xlPasteValues

End Sub

edited Jun 20 '19 at 13:55

answered Jun 20 '19 at 13:47

Error 1004

7,877
3
23
46

I'm sorry, what does Option Explicit actually do? I'm a bit of a newbie to this coding enviroment :) – John Doe Jun 20 '19 at 13:49
2

@John Doe Here's the docs: https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/option-explicit-statement. In short, `Forces explicit declaration of all variables`. – BigBen Jun 20 '19 at 13:52
@BigBen thanks for the correction. i have remove my comment to avoid confusion. – Error 1004 Jun 20 '19 at 13:53
@JohnDoe in addition to BigBen link, is better to use `Option Explicit` to helps you to identify which variables from your code is not declared. – Error 1004 Jun 20 '19 at 13:56
great piece of advice, I'll use it from now on for debugging :)… i've added more context in my question now, could it help? – John Doe Jun 20 '19 at 13:58

"Runtime Error 1004: Copy method of worksheet class failed " in VBA on first copy attempt

1 Answers1