Excel VBA需求：插入新行后向下填充公式与格式

Got it, let's tackle this VBA task step by step. Your core row-insertion logic is already in place—we just need to add the part that copies the formatting, merged cell structure, and formulas from row 36 to all the new rows you insert.

Complete VBA Code #

Sub AddRowsAndFillFormatting()
    Dim wbSource As Workbook
    Dim wsSource As Worksheet
    Dim wbTarget As Workbook
    Dim wsTarget As Worksheet
    Dim rowstoadd As Long
    Dim lastNewRow As Long
    
    ' Set explicit references to avoid relying on active workbooks/sheets
    Set wbSource = Workbooks("ProjectCostReport") ' Ensure this name matches exactly
    Set wsSource = wbSource.Worksheets("BRF Macro")
    Set wbTarget = Workbooks("NEW UKOTC 2020 BRF.xlsx")
    Set wsTarget = wbTarget.Worksheets("T&M BRF")
    
    ' Calculate rows to add if the condition is met
    If wsSource.Range("I1").Value > 20 Then
        rowstoadd = wsSource.Range("I1").Value - 20
        lastNewRow = 37 + rowstoadd - 1 ' Get the final row of the inserted range
        
        ' Insert new rows starting at row 37
        wsTarget.Rows("37:" & lastNewRow).Insert Shift:=xlDown
        
        ' Copy formatting, merged cells, and formulas from row 36 to new rows
        With wsTarget
            ' Copy the full range from row 36
            .Range("B36:L36").Copy
            ' Paste formatting first (includes merged B/C columns)
            .Range("B37:L" & lastNewRow).PasteSpecial Paste:=xlPasteFormats
            ' Paste formulas for H, J, L columns (and any others in the range)
            .Range("B37:L" & lastNewRow).PasteSpecial Paste:=xlPasteFormulas
            
            ' Clear clipboard to remove the lingering copy selection
            Application.CutCopyMode = False
        End With
    End If
End Sub

Key Details Explained #

• Explicit References: Using named workbook/worksheet variables makes the code more reliable—no more issues if the active sheet changes unexpectedly.
• Row Range Adjustment: lastNewRow fixes the end row calculation so we don't insert an extra row beyond what's needed.
• Copying Logic: The double PasteSpecial ensures both the visual formatting (like merged B/C cells) and the functional formulas in H, J, L columns are applied to every new row.
• Merged Cells: Since B36 and C36 are merged, copying the range automatically applies that merged structure to the new rows' B and C columns—no extra code required here.

Quick Notes #

• Double-check that your workbook names match exactly (including file extensions if the files are open).
• If you want to make the code more flexible later, you could replace hardcoded row numbers (36, 37) with named ranges, but this version works perfectly for your specified requirements.

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