Excel VBA跨工作簿数据复制及动态行适配问题咨询

我明白你作为VBA新手遇到的困扰——既要修正现有复制粘贴的逻辑错误，还要让代码自动适配原工作表新增的行，不用每次手动改硬编码的行号。先帮你梳理下原代码里的问题，再给出优化后的完整方案：

原代码的主要问题 #

• 拼写错误：Paste1Special 应该是 PasteSpecial
• 硬编码行号：比如Range("B2:B28")这类固定范围，新增行后无法自动识别
• 插入行与粘贴的逻辑冲突：插入行后粘贴范围没有动态调整，导致数据错位
• 语法错误：Columns().AutoFit 多了括号，Else _ 的下划线多余
• 未指定粘贴类型：PasteSpecial 没明确xlPasteValues等类型，容易导致格式混乱

优化后的完整代码 #

Sub CopyInfo()
    On Error GoTo Err_Execute
    
    Dim wb1 As Workbook, wb2 As Workbook
    Dim shtName As String
    Dim lastRowDataForm As Long ' 存储dataform表的最后一行行号
    
    ' 假设wb1是当前工作簿，wb2是目标新工作簿，请根据实际情况调整初始化逻辑
    Set wb1 = ThisWorkbook
    ' 示例：Set wb2 = Workbooks.Open("C:\目标工作簿路径.xlsx")
    shtName = "目标工作表名称" ' 替换成你的目标工作表名称
    
    ' 动态获取dataform表的有效数据最后一行（从B2往下定位非空行）
    lastRowDataForm = wb1.Sheets("dataform").Range("B2").End(xlDown).Row
    
    ' 复制L2:N2到目标表A2位置插入整行，并粘贴值
    wb1.Sheets("dataform").Range("L2:N2").Copy
    wb2.Sheets(shtName).Range("A2").EntireRow.Insert Shift:=xlDown
    wb2.Sheets(shtName).Range("A2:C2").PasteSpecial xlPasteValues
    
    ' 复制B列动态范围数据，插入到目标表D2开始的行
    wb1.Sheets("dataform").Range("B2:B" & lastRowDataForm).Copy
    wb2.Sheets(shtName).Range("D2").EntireRow.Insert Shift:=xlDown
    wb2.Sheets(shtName).Range("D2:D" & lastRowDataForm).PasteSpecial xlPasteValues
    
    ' 复制D列动态范围数据，插入到目标表F2开始的行
    wb1.Sheets("dataform").Range("D2:D" & lastRowDataForm).Copy
    wb2.Sheets(shtName).Range("F2").EntireRow.Insert Shift:=xlDown
    wb2.Sheets(shtName).Range("F2:F" & lastRowDataForm).PasteSpecial xlPasteValues
    
    ' 调用更新过程，传递必要参数
    UpdateData wb1, wb2, shtName, lastRowDataForm
    
    ' 复制Setlist表G2到目标表E列动态范围，若原逻辑是固定27行可自行调整
    wb1.Sheets("Setlist").Range("G2").Copy
    wb2.Sheets(shtName).Range("E2:E" & (1 + lastRowDataForm)).EntireRow.Insert Shift:=xlDown
    wb2.Sheets(shtName).Range("E2:E" & (1 + lastRowDataForm)).PasteSpecial xlPasteValues
    
    ' 自动调整列宽
    wb2.Sheets(shtName).Columns.AutoFit
    
    ' 释放剪贴板资源
    Application.CutCopyMode = False
    
Err_Execute:
    If Err.Number = 0 Then
        MsgBox "所有数据已复制完成！"
    Else
        MsgBox "错误描述：" & Err.Description
    End If
End Sub

Sub UpdateData(wb1 As Workbook, wb2 As Workbook, shtName As String, lastRow As Long)
    ' 复制D列动态范围数据，粘贴到dataform表的E、F列
    wb1.Sheets("dataform").Range("D2:D" & lastRow).Copy
    wb1.Sheets("dataform").Range("E2:E" & lastRow).PasteSpecial xlPasteValues
    wb1.Sheets("dataform").Range("F2:F" & lastRow).PasteSpecial xlPasteValues
    
    ' 复制F列动态范围数据，插入到目标表G2开始的行
    wb1.Sheets("dataform").Range("F2:F" & lastRow).Copy
    wb2.Sheets(shtName).Range("G2").EntireRow.Insert Shift:=xlDown
    wb2.Sheets(shtName).Range("G2:G" & lastRow).PasteSpecial xlPasteValues
    
    ' 释放剪贴板资源
    Application.CutCopyMode = False
End Sub

关键改进说明 #

• 动态获取最后一行：用Range("B2").End(xlDown).Row自动识别dataform表的有效数据行，原表新增行后无需手动修改代码
• 优化插入行逻辑：改用EntireRow.Insert插入整行，避免局部范围插入导致的数据错位；粘贴时匹配动态范围，确保数据完整对应
• 规范语法与资源管理：修正拼写和语法错误，添加Application.CutCopyMode = False释放剪贴板，避免内存占用
• 参数化传递：将update过程改为带参数的UpdateData，直接传递工作簿、工作表名称和行号，避免全局变量依赖，代码更健壮
• 明确粘贴类型：所有PasteSpecial都指定xlPasteValues，确保只粘贴数据值，避免格式干扰

注意：请根据实际场景替换代码里的wb2初始化逻辑（比如打开指定路径的工作簿）和shtName目标工作表名称，确保运行时两个工作簿处于打开状态。

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