Excel 2016 VBA跨文件复制数据报错求助：运行时错误1004

问题核心原因 #

你的代码触发1004错误主要有两个关键问题：

• 目标工作表索引错误：需求是复制到Home的第2到第6张工作表，但代码中wbDest.Sheets(i)（i从1到5）指向的是第1到第5张表，若Home工作表数量不足或操作不符合预期，会直接触发错误。
• 对象引用逻辑不严谨：缺少源文件存在性检查，且复制操作的对象引用可能因工作簿激活状态出现异常。

修正后的VBA代码 #

Sub CopyCarData()
    Dim wbSource As Workbook
    Dim wbDest As Workbook
    Dim wsDest As Worksheet
    Dim i As Integer
    Dim sourceFilePath As String
    Dim destFilePath As String
    
    ' 定义文件基础路径
    destFilePath = "C:\Destination\Home.xlsx"
    sourceFilePath = "C:\Source\Car"
    
    ' 关闭屏幕更新提升运行效率
    Application.ScreenUpdating = False
    
    ' 打开目标工作簿
    Set wbDest = Workbooks.Open(destFilePath)
    
    For i = 1 To 5
        ' 拼接当前源文件完整路径
        Dim fullSourcePath As String
        fullSourcePath = sourceFilePath & i & ".xlsx"
        
        ' 检查源文件是否存在，避免打开不存在的文件报错
        If Dir(fullSourcePath) <> "" Then
            Set wbSource = Workbooks.Open(fullSourcePath)
            
            ' 指向目标工作簿的第i+1张工作表（对应第2到第6张）
            Set wsDest = wbDest.Sheets(i + 1)
            
            ' 明确指定复制的目标区域，减少对象引用歧义
            wbSource.Sheets(1).Range("E4:E124").Copy Destination:=wsDest.Range("A1")
            
            ' 关闭源文件，不保存任何修改
            wbSource.Close SaveChanges:=False
        Else
            ' 提示缺失的文件，方便排查问题
            MsgBox "源文件 " & fullSourcePath & " 不存在，跳过该文件。"
        End If
    Next i
    
    ' 保存并关闭目标工作簿
    wbDest.Close SaveChanges:=True
    
    ' 恢复屏幕更新
    Application.ScreenUpdating = True
End Sub

关键修改说明 #

• 目标工作表索引修正：将wbDest.Sheets(i)改为wbDest.Sheets(i+1)，确保对应Home的第2到第6张工作表。
• 增加文件存在性检查：通过Dir()函数验证源文件是否存在，避免因文件缺失触发错误。
• 优化复制语句：明确指定Destination参数，消除对象引用的歧义。
• 关闭屏幕更新：减少界面闪烁，提升宏的运行速度。
• 明确源文件关闭规则：指定SaveChanges:=False，避免误修改源文件。

内容的提问来源于stack exchange，提问作者Roberto Campagnola