Excel macro to combine workbooks, Runtime Error 1004

Question

I am attempting to merge specific xls files into one sheet, but I get a runtime error 1004 saying " Copy method of Worksheet class failed" I am thinking this is because I am trying to merge over 100 files?

Sub GetSheets()
    Path = "C:\Users\..."
    Filename = Dir(Path & "*100.00mA.isd.xls")

Do While Filename <> ""

    Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
    For Each Sheet In ActiveWorkbook.Sheets
    **Sheet.Copy After:=ThisWorkbook.Sheets(1)**
    Next Sheet
    Workbooks(Filename).Close
    Filename = Dir()

Loop
End Sub

Does it copy some sheets and then eventually fail? Or does it fail on the very first pass? — David Zemens, Jun 12 '14 at 18:59
The number of sheets in an Excel workbook is based on the memory on your system. How many sheets does it copy before it fails? — Dave.Gugg, Jun 12 '14 at 19:48
I think @DaveU nailed it. Your problem is using ActiveWorkbook. See [THIS](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) as well so you can improve other parts of your code. — L42, Jun 13 '14 at 01:17

score 2 · Accepted Answer · answered Jun 13 '14 at 00:31

I have occssionally run into a "timing" problem with xl2003 and ActiveWorkbook, maybe this is causing your problem. Sometimes the VBA code gets to the ActiveWorkbook line before Excel has the new workbook fully opened, consequently, ThisWorkbook becomes the ActiveWorkbook. The way to work around this is to specifically assign a variable to the new workbook.

Sub GetSheets()
    Dim wB As Workbook '<=New
    Path = "C:\Users\..."
    Filename = Dir(Path & "*100.00mA.isd.xls")

    Do While Filename <> ""

        Set wB = Workbooks.Open(Filename:=Path & Filename, ReadOnly:=True) '<=New
        For Each Sheet In wB.Sheets '<=New
            Sheet.Copy After:=ThisWorkbook.Sheets(1)
        Next Sheet
        wB.Close '<=New
        Filename = Dir()
    Loop
End Sub

score 0 · Answer 2 · answered Jun 15 '14 at 09:08

There is another situation, where the described runtime error will appear:

In the case that your target workbook is an Excel 97-2003 file (.xls) and your source workbook is an Excel 2007 (or higher) file (.xlsx).

Having this combination, the sheet.copy method will cause the same runtime error.

You may check the different workbook formats by reading the ActiveWorkbook.FileFormat property.

Excel macro to combine workbooks, Runtime Error 1004

2 Answers2

Linked