Excel VBA编写循环实现跨工作表按固定间隔复制指定单元格ID

• 需将特定ID编号从Hoja1工作表复制到当前工作表，ID位置按固定规律排布：源表起始位置为B8单元格，后续按行号每次加21的规则顺延，依次为B29、B50、B71……
• 已录制手动逐行写入公式的宏代码，需添加循环结构实现批量处理，替代重复的硬编码操作。

原录制宏代码如下：

Sub Macro3()

    ActiveCell.FormulaR1C1 = "=Hoja1!R[-4]C2"
    Range("A13").Select
    
    ActiveCell.FormulaR1C1 = "=Hoja1!R[16]C2"
    Range("A14").Select
    
    ActiveCell.FormulaR1C1 = "=Hoja1!R[36]C2"
    Range("A15").Select
    
    ActiveCell.FormulaR1C1 = "=Hoja1!R[56]C2"
    Range("A16").Select
    
End Sub

录制宏生成的Select/ActiveCell写法执行效率低，且硬编码了固定单元格位置，无法适配批量场景。可以通过定义循环变量，按位置规律自动计算每个单元格的引用，代码如下：

Sub BatchCopyID()
    Dim srcStartRow As Long, tgtStartRow As Long
    Dim i As Long, totalIDCount As Long
    
    ' 配置基础参数
    srcStartRow = 8 ' 源表第一个ID所在行号，对应B8
    tgtStartRow = 12 ' 目标表第一个ID写入的行号，对应A12
    totalIDCount = 10 ' 按需修改为实际需要处理的ID总数量
    
    ' 关闭屏幕更新提升执行速度
    Application.ScreenUpdating = False
    
    ' 循环批量写入公式
    For i = 0 To totalIDCount - 1
        ' 目标单元格为A列，行号从tgtStartRow开始每次+1
        ' 源单元格为Hoja1表B列，行号从srcStartRow开始每次+21
        Cells(tgtStartRow + i, "A").FormulaR1C1 = _
            "=Hoja1!R" & (srcStartRow + 21 * i - (tgtStartRow + i)) & "C2"
    Next i
    
    ' 恢复屏幕更新
    Application.ScreenUpdating = True
End Sub

参数调整说明 #

• 若需处理的ID总数变化，直接修改totalIDCount的赋值即可，不需要逐行编写公式
• 若需要自动识别源表ID数量（直到B列空单元格停止），可以把固定次数循环替换为以下逻辑：

i = 0
Do While Sheets("Hoja1").Cells(srcStartRow + 21 * i, "B") <> ""
    Cells(tgtStartRow + i, "A").FormulaR1C1 = _
        "=Hoja1!R" & (srcStartRow + 21 * i - (tgtStartRow + i)) & "C2"
    i = i + 1
Loop

• 如果不需要保留公式关联，直接复制ID值，可以把写入公式的代码替换为直接赋值：

Cells(tgtStartRow + i, "A").Value = Sheets("Hoja1").Cells(srcStartRow + 21 * i, "B").Value

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