VBA使用动态变量定义Range范围复制数据时报1004错误解决方案咨询

错误成因 #

• 核心触发原因：代码中Cells(2, 22)、Cells(LastRow, 24)未显式指定所属工作表，VBA默认将无归属的Cells指向当前活动工作表。如果运行代码时活动工作表不是Checklist，相当于在Checklist表的Range对象中传入了其他工作表的单元格坐标，区域归属不匹配直接触发1004运行时错误。
• 次要风险：如果Checklist表V列V2以下全部为空，End(xlDown)会直接跳转至Excel表格最大行，LastRow数值异常也可能触发该错误。

解决方案 #

方案1：显式绑定Cells所属工作表 #

直接修改报错行代码，给所有Cells对象加上Checklist表的归属声明：

Sheets("Checklist").Range(Sheets("Checklist").Cells(2, 22), Sheets("Checklist").Cells(LastRow, 24)).Copy

方案2：用With语句简化引用（推荐） #

使用With语句统一绑定工作表，避免重复写工作表名，同时消除原代码无意义的GoTo跳转，优化后完整代码如下：

Sub 复制零件数据()
    Dim LastRow As Long, Parts As Long
    ' 处理Checklist表源数据
    With Sheets("Checklist")
        LastRow = .Range("V2").End(xlDown).Row
        ' 前缀带.的对象自动绑定With指定的工作表
        .Range(.Cells(2, 22), .Cells(LastRow, 24)).Copy
    End With
    
    ' 计算Pipeline表粘贴起始行
    With Sheets("Pipeline")
        If .Range("D4") = "" Then
            Parts = 4
        ElseIf .Range("D5") = "" Then
            Parts = 5
        Else
            Parts = .Range("D4").End(xlDown).Row + 1
        End If
        .Cells(Parts, 4).PasteSpecial xlPasteValues
    End With
    
    Application.CutCopyMode = False
End Sub

进阶优化：跳过剪贴板直接赋值 #

如果仅需要同步数值不需要复制格式，可以直接通过区域赋值实现，运行效率更高，也不会占用系统剪贴板：

Sub 高效复制零件数据()
    Dim LastRow As Long, Parts As Long, sourceRng As Range
    ' 定义源数据区域
    With Sheets("Checklist")
        LastRow = .Range("V2").End(xlDown).Row
        Set sourceRng = .Range(.Cells(2, 22), .Cells(LastRow, 24))
    End With
    
    ' 计算粘贴起始行
    With Sheets("Pipeline")
        If .Range("D4") = "" Then
            Parts = 4
        ElseIf .Range("D5") = "" Then
            Parts = 5
        Else
            Parts = .Range("D4").End(xlDown).Row + 1
        End If
        ' 直接赋值，不需要Copy/Paste
        .Cells(Parts, 4).Resize(sourceRng.Rows.Count, sourceRng.Columns.Count).Value = sourceRng.Value
    End With
End Sub

内容的提问来源于stack exchange，提问作者Ek.murg