Excel VBA运行时错误1004求助：数据复制代码报错

可能的错误原因及排查步骤 #

• 工作表名称是否匹配：确认文件里确实存在test、AllocatedAllFile、genAllFile这三个工作表，检查名称的拼写、空格、特殊字符，别和实际表名有出入。
• Rows.Count未指定工作表：原代码里Rows.count默认用当前活动工作表的行数，要是活动表不是目标表，会导致计算的行数出错，进而引用不存在的单元格，必须给Rows.Count指定具体工作表。
• 数据行数逻辑异常：计算lastDRS时，如果genAllFile表第3列的行数比AllocatedAllFile的还多，会导致lastDRS变成负数，循环起始值大于终止值；另外test表从第2行开始的有效行数，必须等于oldLastRow - lastDRS，否则会出现填充不完或引用空行之外的单元格。
• 列数超出范围：循环j从1到51，要确认test表和AllocatedAllFile表都有至少51列，否则引用第51列时会触发错误。

修复后的代码 #

Sub transfterDealColResults()
    Dim wsTest As Worksheet
    Dim wsAllocated As Worksheet
    Dim wsGen As Worksheet
    
    ' 绑定工作表并检查是否存在
    On Error Resume Next
    Set wsTest = ThisWorkbook.Sheets("test")
    Set wsAllocated = ThisWorkbook.Sheets("AllocatedAllFile")
    Set wsGen = ThisWorkbook.Sheets("genAllFile")
    On Error GoTo 0
    
    If wsTest Is Nothing Or wsAllocated Is Nothing Or wsGen Is Nothing Then
        MsgBox "指定的工作表不存在，请检查名称！"
        Exit Sub
    End If
    
    ' 删除L列逻辑，指定工作表避免歧义
    On Error Resume Next
    If wsTest.Cells(1, 12) = "Colour Name" Then
        wsTest.Columns("L").Delete
    End If
    On Error GoTo 0
    
    Dim oldLastRow As Long
    Dim lastDRS As Long
    Dim newRowStart As Long
    Dim totalRowsToFill As Long
    
    ' 指定工作表计算行数，避免活动表干扰
    oldLastRow = wsAllocated.Cells(wsAllocated.Rows.Count, 3).End(xlUp).Row
    lastDRS = oldLastRow - wsGen.Cells(wsGen.Rows.Count, 3).End(xlUp).Row + 1
    totalRowsToFill = oldLastRow - (lastDRS + 1) + 1
    
    ' 检查test表数据行数是否足够
    If wsTest.Cells(wsTest.Rows.Count, 1).End(xlUp).Row - 1 < totalRowsToFill Then
        MsgBox "test表的数据行数不足，无法完成填充！"
        Exit Sub
    End If
    
    ' 循环填充数据，统一使用绑定的工作表对象
    For j = 1 To 51
        newRowStart = 2
        For i = lastDRS + 1 To oldLastRow
             wsAllocated.Cells(i, j) = wsTest.Cells(newRowStart, j)
             newRowStart = newRowStart + 1
        Next i
    Next j
    
End Sub

优化说明 #

• 用变量绑定工作表，既减少重复代码，又能提前检查工作表是否存在，直接给出明确错误提示。
• 增加数据行数预检查，避免运行到一半才报错。
• 所有行数计算都指定对应工作表，彻底解决活动表导致的计算偏差问题。

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