Excel VBA如何跨工作表复制数据并动态调整VLOOKUP公式

实现逻辑 #

直接遍历Sheet1的所有有效数据行，以你已经做好的第一个表单为模板逐行生成新表单，既可以直接写入值，也可以仅修改ID触发VLOOKUP自动匹配其他字段，无需手动修改公式规则。

完整VBA代码 #

Sub 批量生成表单()
    Dim wsSource As Worksheet, wsTarget As Worksheet
    Dim lastRow As Long, i As Long
    Dim templateRng As Range, destCell As Range
    Dim formHeight As Long
    
    ' 绑定对应工作表
    Set wsSource = Worksheets("Sheet1")
    Set wsTarget = Worksheets("Sheet2")
    ' 定义你已做好的模板范围、表单高度（你原模板为B6:Q20，共15行）
    Set templateRng = wsTarget.Range("B6:Q20")
    formHeight = templateRng.Rows.Count
    
    ' 自动获取Sheet1最后一行有效数据（假设ID在A列，第1行为表头）
    lastRow = wsSource.Cells(wsSource.Rows.Count, "A").End(xlUp).Row
    
    ' 遍历Sheet1每一条数据（跳过表头从第2行开始）
    For i = 2 To lastRow
        ' 定位每次粘贴的起始位置，表单之间保留2行空行分隔
        Set destCell = wsTarget.Cells(wsTarget.Rows.Count, "B").End(xlUp)
        If destCell.Row >= templateRng.Row Then
            Set destCell = destCell.Offset(2)
        Else
            ' 首次生成从模板下方开始，避免覆盖原始模板
            Set destCell = templateRng.Offset(formHeight + 2)
        End If
        
        ' 复制模板格式和内容
        templateRng.Copy
        destCell.PasteSpecial Paste:=xlPasteColumnWidths
        destCell.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
        destCell.PasteSpecial Paste:=xlPasteFormulas
        
        ' 替换当前表单的字段值，Offset参数根据你模板内字段的实际位置调整
        With destCell
            ' 方案1：直接写入值，无需保留VLOOKUP公式
            .Offset(0, 0).Value = wsSource.Cells(i, "A").Value ' 对应ID
            .Offset(1, 0).Value = Format(Date, "dd/mmm/yyyy") ' 自动填充当日日期
            .Offset(2, 0).Value = wsSource.Cells(i, "B").Value ' 对应Name
            .Offset(3, 0).Value = wsSource.Cells(i, "C").Value ' 对应Salary
            
            ' 方案2：保留VLOOKUP公式时，仅替换ID即可，其他字段会自动匹配
            ' .Offset(0, 0).Value = wsSource.Cells(i, "A").Value
        End With
    Next i
    
    ' 清空剪贴板
    Application.CutCopyMode = False
    wsTarget.Activate
    destCell.Select
End Sub

注意事项 #

• Offset的第一个参数为行偏移、第二个为列偏移，以粘贴起始单元格为基准，你可以根据自己模板内字段的实际位置调整参数。
• 后续Sheet1新增数据后无需修改代码，运行即可自动识别新数据生成对应表单。
• 如果不需要保留原始模板，可以在运行前清空Sheet2内除模板外的历史内容，避免重复生成。

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