VBA跨工作簿取数报错error 1004：method range of object _global failed

错误核心原因 #

报错行Range("A2:P2000" & l_Row).Copy存在语法逻辑错误：你原本想选取从A2到P列最后一行（l_Row）的区域，但字符串拼接后会生成类似A2:P2000123的无效单元格引用，Excel无法识别该范围，因此抛出1004错误。

修正后的完整代码 #

Sub Get_Abc_Rates_Collate()
    Dim n As Long
    Dim wbIndex As Long
    Dim masterWB As Workbook
    Dim sourceWB As Workbook
    Dim sourceWS As Worksheet
    Dim destWS As Worksheet
    Dim l_Row As Long
    Dim l_Dest As Long
    Dim filePath As String

    Application.DisplayAlerts = False
    Application.ScreenUpdating = False

    Set masterWB = ThisWorkbook
    Set destWS = masterWB.Sheets("abc_Rates_CollateMS")

    With Application.FileDialog(msoFileDialogOpen)
        .AllowMultiSelect = True
        .Title = "选择目标文件"
        .Show

        n = .SelectedItems.Count

        For wbIndex = 1 To n
            filePath = .SelectedItems(wbIndex)
            Set sourceWB = Workbooks.Open(filePath)
            
            For Each sourceWS In sourceWB.Worksheets
                If sourceWS.Name = "abc_Collate" Then
                    ' 获取源表A列最后一行行号
                    l_Row = sourceWS.Cells(sourceWS.Rows.Count, "A").End(xlUp).Row
                    ' 复制A2到P列最后一行的有效数据区域
                    sourceWS.Range("A2:P" & l_Row).Copy
                    
                    ' 获取目标表A列下一个空行行号
                    l_Dest = destWS.Cells(destWS.Rows.Count, "A").End(xlUp).Row + 1
                    ' 粘贴到目标表对应位置
                    destWS.Range("A" & l_Dest).PasteSpecial xlPasteAll
                    
                    Exit For ' 找到目标表后直接终止循环，避免无效遍历
                End If
            Next sourceWS
            
            sourceWB.Close savechanges:=False
        Next wbIndex
    End With

    destWS.Range("C1").CurrentRegion.EntireColumn.AutoFit

    ' 恢复Excel默认设置
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub

关键修改说明 #

• 修复范围引用错误：将错误的Range("A2:P2000" & l_Row)改为sourceWS.Range("A2:P" & l_Row)，确保单元格范围合法，同时明确指定操作的工作表对象，避免依赖ActiveSheet。
• 移除Select/Activate操作：直接通过对象变量（如masterWB、sourceWS）操作工作簿和工作表，彻底消除因窗口切换导致的不稳定问题。
• 变量优化：
  • 将Integer类型的计数变量改为Long，避免文件数量超过32767时的溢出问题。
  • 强制声明所有变量（原代码中Path未声明），建议在模块顶部添加Option Explicit，可自动检测未声明的变量。
• 逻辑优化：找到目标工作表后用Exit For跳出循环，减少无效遍历；最后恢复Excel的显示提示和屏幕更新功能，避免影响后续操作。
• 兼容不同Excel版本：用sourceWS.Rows.Count替代硬编码的1048576，适配不同版本Excel的行数量差异。

内容的提问来源于stack exchange，提问作者BIPLAB GUPTA