Excel宏合并工作簿，运行时错误1004 [英] Excel macro to combine workbooks, Runtime Error 1004

问题描述

我正在尝试将特定的xls文件合并到一个工作表中，但是出现运行时错误1004，提示"Worksheet类的复制方法失败"，我想这是因为我正在尝试合并100个以上的文件?

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

推荐答案

我偶尔会遇到xl2003和ActiveWorkbook的时序"问题，也许这是造成您的问题的原因.有时，在Excel完全打开新工作簿之前，VBA代码到达ActiveWorkbook行，因此，ThisWorkbook成为ActiveWorkbook.解决此问题的方法是为新工作簿专门分配一个变量.

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

这篇关于Excel宏合并工作簿，运行时错误1004的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！