如何用VBA宏批量将Excel记录迁移至指定单元格并追加结果

需求说明 #

假设源工作表有4行数据（第2至5行），目标工作表的输出格式要求如下：

A2 ->W3 和 CY6
B2 ->G2
C2->AI4
D2->CH5
E2->AE4
G2->DA6
H2->CQ6

A3 ->W8 和 CY11
B3 ->G7
C3->AI9
D3->CH10
E3->AE9
G3->DA11
H3->CQ11

A4 ->W13 和 CY16
B4 ->G12
C4->AI14
D4->CH15
E4->AE14
G4->DA16
H4->CQ16

A5->W18 和 CY21
B5 ->G17
C5->AI19
D5->CH20
E5->AE19
G5->DA21
H5->CQ21

后续行遵循相同规则：每行源数据对应目标区域的行间隔为5（如A2对应W3、CY6；A3对应W8、CY11，行号差5）。目前已实现单行数据迁移，但无法正确完成自动化批量追加，需用VBA宏完成900次重复操作。

当前VBA代码 #

' 获取源工作表的最后一行
lastRow = sourceSheet.Cells(sourceSheet.Rows.Count, 1).End(xlUp).Row

' 逐行迁移数据
For targetRow = 2 To lastRow
        With targetSheet
        .Cells((targetRow - 1) * 4, 23).Value = sourceSheet.Cells(targetRow, 1).Value ' 迁移A列到目标W3
        .Cells(((targetRow - 1) * 4) + 3, 99).Value = sourceSheet.Cells(targetRow, 1).Value ' 迁移A列到目标CY6
        .Cells(((targetRow - 1) * 4) + 1, 7).Value = sourceSheet.Cells(targetRow, 2).Value ' 迁移B列到目标G2
        .Cells(((targetRow - 1) * 4) + 2, 35).Value = sourceSheet.Cells(targetRow, 3).Value ' 迁移C列到目标AI4
        .Cells(((targetRow - 1) * 4) + 3, 31).Value = sourceSheet.Cells(targetRow, 4).Value ' 迁移D列到目标CH5
        .Cells(((targetRow - 1) * 4) + 2, 31).Value = sourceSheet.Cells(targetRow, 5).Value ' 迁移E列到目标AE4
        .Cells(((targetRow - 1) * 4) + 3, 106).Value = sourceSheet.Cells(targetRow, 7).Value ' 迁移G列到目标DA6
        .Cells(((targetRow - 1) * 4) + 1, 93).Value = sourceSheet.Cells(targetRow, 8).Value ' 迁移H列到目标CQ6
    End With
    Next targetRow

问题分析与修正 #

当前代码的行号计算逻辑错误：示例中每行源数据对应的目标行间隔是5，但代码用(targetRow-1)*4的计算方式，导致行号偏移不符合要求。

修正后的代码基于起始行 + (源行索引-2)*5的规则计算目标行，完全匹配示例规则：

Sub BatchMigrateData()
    Dim sourceSheet As Worksheet
    Dim targetSheet As Worksheet
    Dim lastRow As Long
    Dim srcRow As Long
    Dim baseOffset As Long ' 每行源数据对应的目标行偏移量

    ' 替换为实际的源工作表和目标工作表名称
    Set sourceSheet = ThisWorkbook.Worksheets("源表")
    Set targetSheet = ThisWorkbook.Worksheets("目标表")

    ' 获取源工作表最后一行
    lastRow = sourceSheet.Cells(sourceSheet.Rows.Count, 1).End(xlUp).Row

    ' 逐行处理源数据
    For srcRow = 2 To lastRow
        ' 计算当前源行对应的目标行基准偏移：第2行源数据对应偏移0，第3行对应5，以此类推
        baseOffset = (srcRow - 2) * 5
        
        With targetSheet
            ' A列 -> W3 + baseOffset, CY6 + baseOffset
            .Cells(3 + baseOffset, 23).Value = sourceSheet.Cells(srcRow, 1).Value
            .Cells(6 + baseOffset, 99).Value = sourceSheet.Cells(srcRow, 1).Value
            
            ' B列 -> G2 + baseOffset
            .Cells(2 + baseOffset, 7).Value = sourceSheet.Cells(srcRow, 2).Value
            
            ' C列 -> AI4 + baseOffset
            .Cells(4 + baseOffset, 35).Value = sourceSheet.Cells(srcRow, 3).Value
            
            ' D列 -> CH5 + baseOffset
            .Cells(5 + baseOffset, 31).Value = sourceSheet.Cells(srcRow, 4).Value
            
            ' E列 -> AE4 + baseOffset
            .Cells(4 + baseOffset, 31).Value = sourceSheet.Cells(srcRow, 5).Value
            
            ' G列 -> DA6 + baseOffset
            .Cells(6 + baseOffset, 106).Value = sourceSheet.Cells(srcRow, 7).Value
            
            ' H列 -> CQ6 + baseOffset
            .Cells(6 + baseOffset, 93).Value = sourceSheet.Cells(srcRow, 8).Value
        End With
    Next srcRow
End Sub

修正说明 #

1. 行号计算逻辑改为baseOffset = (srcRow - 2)*5，完全匹配示例中每行间隔5行的规则；
2. 每个目标单元格行号采用固定起始行 + baseOffset的方式，逻辑直观不易出错；
3. 显式定义工作表对象，避免依赖ActiveSheet，提升代码稳定性；
4. 修正了H列迁移的目标行号错误，匹配示例中H2对应CQ6的要求。

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