"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!

Answer 1

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