如何在VBA中复制粘贴行且不更改数据，同时操作两个工作表

核心处理思路 #

• 避免公式引用自动更新：直接赋值单元格的Formula属性字符串，替代普通复制粘贴（普通粘贴会自动调整单元格引用）
• 多工作表批量执行：将核心逻辑封装为独立子过程，按需调用目标工作表

完整代码 #

Private Sub CommandButton1_Click()
    Dim addRows As Variant
    Dim targetSheets As Variant
    Dim ws As Worksheet
    
    ' 获取用户输入的新增行数，添加合法性判断
    addRows = InputBox("请输入需要新增的行数：")
    If Not IsNumeric(addRows) Or addRows < 1 Then
        MsgBox "请输入有效的正整数！", vbExclamation
        Exit Sub
    End If
    
    ' 指定需要操作的两个工作表，可根据实际名称修改
    targetSheets = Array("Sheet1", "Sheet2")
    
    ' 遍历目标工作表执行操作
    For Each ws In ThisWorkbook.Worksheets
        If UBound(Filter(targetSheets, ws.Name)) > -1 Then
            InsertRowsWithFixedFormula ws, addRows
        End If
    Next ws
    
    ' 清理剪贴板
    Application.CutCopyMode = False
End Sub

' 封装核心操作：在指定工作表的第3行位置插入指定行数，保留原公式引用不变
Private Sub InsertRowsWithFixedFormula(ws As Worksheet, rowCount As Integer)
    Dim i As Integer
    Dim originalRow As Range
    Dim newRow As Range
    
    With ws
        ' 禁用屏幕更新和事件，提升效率并避免干扰
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        
        For i = 1 To rowCount
            ' 在第3行插入新行
            .Rows(3).Insert Shift:=xlDown
            
            ' 定义原行（插入后原第3行变为第4行）和新行
            Set originalRow = .Rows(4)
            Set newRow = .Rows(3)
            
            ' 复制格式和值，然后单独赋值公式（保持引用完全一致）
            originalRow.Copy
            newRow.PasteSpecial xlPasteFormats
            newRow.PasteSpecial xlPasteValues
            
            ' 遍历原行的单元格，将公式字符串直接赋值给新行
            Dim cell As Range
            For Each cell In originalRow.Cells
                If cell.HasFormula Then
                    newRow.Cells(cell.Column).Formula = cell.Formula
                End If
            Next cell
        Next i
        
        ' 恢复设置
        Application.ScreenUpdating = True
        Application.EnableEvents = True
    End With
End Sub

代码说明 #

• 输入合法性判断：避免用户输入非数字、负数或点击取消导致的运行错误
• 多工作表处理：通过targetSheets数组指定目标工作表，可根据实际需求修改表名
• 公式引用锁定：直接赋值Formula属性字符串，确保复制后的公式与原公式完全一致，无需手动添加$符号锁定引用
• 效率优化：禁用屏幕更新和事件触发，减少操作卡顿

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