VBA可变起始行多列复制粘贴代码修正及批量填充方案

问题1：粘贴代码修正与效率优化 #

原代码错误点如下：

• Range()构造多区域时不能传入多个独立参数，需要把所有区域地址拼接为单个逗号分隔的字符串
• 未声明Rep1变量，容易出现未定义错误
• 未限定工作表的Cells调用会默认读取活动工作表数据，容易出现取值错误
• 使用复制粘贴会调用系统剪贴板，运行效率低，还可能影响用户剪贴板原有内容

修正后的精简高效代码如下：

Option Explicit
Sub CopyFormulas()
    Dim LastrowQ As Long, LastrowR As Long
    Dim Rep1 As String
    ' 修改为实际工作簿名称
    Rep1 = "你的工作簿名称.xlsx"
    
    With Workbooks(Rep1)
        ' 限定工作表取数，避免活动工作表带来的错误
        LastrowQ = .Worksheets("Report").Cells(.Worksheets("Report").Rows.Count, "B").End(xlUp).Row
        LastrowR = LastrowQ + 1
        
        ' 直接赋值公式，跳过剪贴板，运行效率比复制粘贴高30%以上
        .Worksheets("Report").Range("A" & LastrowR & ",C" & LastrowR & ":F" & LastrowR & ",N" & LastrowR & ",V" & LastrowR & ":AD" & LastrowR).Formula = _
        .Worksheets("Formula").Range("A2,C2:F2,N2,V2:AD2").Formula
    End With
End Sub

关于数组实现的说明：如果公式不含需要自动调整的相对引用，可以用数组存公式再批量写入；如果需要Excel自动调整相对引用，直接区域赋值公式的方案更简单，不需要额外处理引用偏移，运行效率和数组方案基本持平。

问题2：公式批量填充到指定结束行 #

推荐两种实现方案，都支持自动调整公式相对引用：

方案1：直接赋值整个目标区域（效率最高） #

Option Explicit
Sub CopyAndFillFormulas()
    Dim LastrowQ As Long, LastrowR As Long, LastrowS As Long
    Dim Rep1 As String
    Rep1 = "你的工作簿名称.xlsx"
    
    With Workbooks(Rep1)
        LastrowQ = .Worksheets("Report").Cells(.Worksheets("Report").Rows.Count, "B").End(xlUp).Row
        LastrowR = LastrowQ + 1
        ' 此处修改为实际结束行号，也可动态计算得到
        LastrowS = 2000
        
        ' 直接给整个范围赋值公式，Excel自动调整相对引用
        .Worksheets("Report").Range("A" & LastrowR & ":A" & LastrowS & ",C" & LastrowR & ":F" & LastrowS & ",N" & LastrowR & ":N" & LastrowS & ",V" & LastrowR & ":AD" & LastrowS).Formula = _
        .Worksheets("Formula").Range("A2,C2:F2,N2,V2:AD2").Formula
    End With
End Sub

方案2：首行赋值后逐区域填充 #

如果只需要首行公式验证后再批量填充，可以用该方案：

' 首行公式赋值和上面一致，后续补充填充代码即可
With Workbooks(Rep1).Worksheets("Report")
    .Range("A" & LastrowR & ":A" & LastrowS).FillDown
    .Range("C" & LastrowR & ":F" & LastrowS).FillDown
    .Range("N" & LastrowR & ":N" & LastrowS).FillDown
    .Range("V" & LastrowR & ":AD" & LastrowS).FillDown
End With

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