Excel宏问题：如何将其他工作表数据追加到Test Summary表最后行

原代码核心问题 #

• 汇总表追加位置定位错误：原代码将Rng1设置为E列整个已使用区域，而非下一个空行，导致覆盖原有数据
• 循环遍历逻辑混乱：i变量的错误使用，使得Rng2的偏移量持续递增，最终仅能定位到活动表最后一个非空列，只复制单一值
• 空值判断冗余：初始判断Rng1.Value <> ""无必要，直接定位最后非空行更高效可靠

修正后的代码 #

Sub test()
' 修正版：实现数据追加，避免覆盖，正确遍历活动表数据
    Dim Rng1 As Range, Rng2 As Range
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim lastRowWs1 As Long, colIndex As Integer
    
    ' 初始化工作表对象
    Set ws1 = ThisWorkbook.Worksheets("Test Summary")
    Set ws2 = ThisWorkbook.ActiveSheet
    Set Rng2 = ws2.Range("F2")
    colIndex = 0 ' 用于遍历活动表的列
    
    ' 定位Test Summary表E列的最后非空行，下一行作为追加起始位置
    lastRowWs1 = ws1.Cells(ws1.Rows.Count, "E").End(xlUp).Row
    ' 如果E2是空的，起始行设为2；否则从最后非空行的下一行开始
    Set Rng1 = ws1.Cells(IIf(lastRowWs1 < 2, 2, lastRowWs1 + 1), "E")
    
    ' 遍历活动表F列开始的非空列
    Do Until IsEmpty(Rng2.Offset(0, colIndex))
        ' 仅处理非空的列数据
        If Rng2.Offset(0, colIndex).Value <> "" Then
            ' 写入引用公式到汇总表对应列
            Rng1.Formula = "='" & ws2.Name & "'!" & Rng2.Offset(0, colIndex).Cells(7, 1).Address
            Rng1.Offset(0, 1).Formula = "='" & ws2.Name & "'!" & Rng2.Offset(0, colIndex).Cells(9, 1).Address
            Rng1.Offset(0, 2).Formula = "='" & ws2.Name & "'!" & Rng2.Offset(0, colIndex).Cells(10, 1).Address
            Rng1.Offset(0, 4).Formula = "='" & ws2.Name & "'!" & Rng2.Offset(0, colIndex).Cells(12, 1).Address
            Rng1.Offset(0, 5).Formula = "='" & ws2.Name & "'!" & Rng2.Offset(0, colIndex).Cells(13, 1).Address
            Rng1.Offset(0, 6).Formula = "='" & ws2.Name & "'!" & Rng2.Offset(0, colIndex).Cells(14, 1).Address
            Rng1.Offset(0, 7).Formula = "='" & ws2.Name & "'!" & Rng2.Offset(0, colIndex).Cells(17, 1).Address
            Rng1.Offset(0, 8).Formula = "='" & ws2.Name & "'!" & Rng2.Offset(0, colIndex).Cells(18, 1).Address
            Rng1.Offset(0, 9).Formula = "='" & ws2.Name & "'!" & Rng2.Offset(0, colIndex).Cells(23, 1).Address
            Rng1.Offset(0, 10).Formula = "='" & ws2.Name & "'!" & Rng2.Offset(0, colIndex).Cells(24, 1).Address
            Rng1.Offset(0, 11).Formula = "='" & ws2.Name & "'!" & Rng2.Offset(0, colIndex).Cells(25, 1).Address
            
            ' 汇总表下移一行，准备下一条数据
            Set Rng1 = Rng1.Offset(1, 0)
        End If
        ' 活动表切换到下一列
        colIndex = colIndex + 1
    Loop
    
    ' 释放对象
    Set Rng1 = Nothing
    Set ws1 = Nothing
    Set Rng2 = Nothing
    Set ws2 = Nothing
End Sub

关键修正说明 #

• 精准定位追加位置：通过ws1.Cells(ws1.Rows.Count, "E").End(xlUp).Row获取E列最后非空行，下一行作为数据写入起点，彻底避免覆盖原有数据
• 修复列遍历逻辑：用固定递增的colIndex遍历活动表列，确保每一列数据都被处理，不会跳过或仅取最后一列
• 简化初始化逻辑：移除冗余的空值判断，优化起始行定位逻辑，代码更简洁高效

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