Excel VBA实现多工作表固定列可变行数据批量合并至目标表

原有代码问题说明 #

• 两个独立子过程仅支持单个表操作，无法批量遍历20个源表
• 存在无效空For循环，无实际业务作用可直接删除
• 第二个过程中Sheet100.Range("H")为非法引用，未指定具体行号会直接触发运行错误
• 未记录上一次数据写入的结束位置，运行时要么出现数据覆盖，要么会残留空行
• 清空目标区域逻辑仅在第一个过程中存在，且清空范围不准确

实现逻辑 #

1. 绑定目标工作表，先清空H4起始位置的原有旧数据，避免历史数据残留
2. 配置所有需要处理的源表及对应的数据起始单元格（示例中两个源表起始位置不一致，按实际情况配置即可，如果所有源表数据起始位置统一可以简化配置）
3. 循环遍历每个源表，读取数据起始单元格的连续数据区域到内存数组
4. 按当前记录的写入起始行，将数组批量写入目标表H列对应位置
5. 更新下一次写入的起始行（当前行+本次写入数据的行数），保证多表数据首尾相接无空行

完整可运行代码 #

Sub BatchMergeSheets()
    Dim targetSht As Worksheet
    Dim sourceList As Variant
    Dim i As Long, nextWriteRow As Long, dataArr As Variant
    Dim rowCount As Long, colCount As Long
    
    ' 绑定目标工作表
    Set targetSht = ThisWorkbook.Sheet100
    ' 初始化写入起始行，从H4开始所以初始行号为4
    nextWriteRow = 4
    
    ' 清空目标表原有数据
    targetSht.Range("H4").CurrentRegion.ClearContents
    ' 若上述清空逻辑不彻底，可替换为下方语句整列清空
    ' targetSht.Range("H:H").ClearContents
    
    ' === 源表配置区，按实际表信息修改 ===
    ' 数组格式：Array(Array(源表对象, 数据起始单元格), Array(源表对象, 数据起始单元格), ...)
    sourceList = Array( _
        Array(Sheet8, "P3"), _
        Array(Sheet7, "P4") _
        ' 剩余18个源表按上述格式逐行添加即可
    )
    ' ==============================================
    
    ' 遍历所有源表批量处理
    For i = LBound(sourceList) To UBound(sourceList)
        ' 读取当前源表的连续数据区域到内存数组
        dataArr = sourceList(i)(0).Range(sourceList(i)(1)).CurrentRegion.Value
        
        ' 跳过无有效数据的工作表
        If IsArray(dataArr) Then
            rowCount = UBound(dataArr, 1)
            colCount = UBound(dataArr, 2)
            
            ' 数组批量写入目标区域
            targetSht.Cells(nextWriteRow, "H").Resize(rowCount, colCount).Value = dataArr
            
            ' 更新下一次写入的起始行，保证无空行、不覆盖
            nextWriteRow = nextWriteRow + rowCount
        End If
    Next i
    
    MsgBox "数据合并完成，共写入" & nextWriteRow - 4 & "行数据", vbInformation
End Sub

使用注意事项 #

• 如果所有20个源表的数据起始位置完全一致（比如均为P3），可以简化配置逻辑，无需逐个填写起始单元格，直接将所有源表对象存入数组，读取数据时统一使用固定起始地址即可
• 代码全程采用内存数组读写，和逐单元格复制的写法相比效率极高，哪怕单表存在数万行数据也可秒级完成
• 后续新增待合并的工作表，只需要在sourceList配置区按格式新增一行即可，无需修改核心逻辑

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