VBA宏问题：复制F列公式至多列时仅粘贴回F列

It looks like your code has a couple of key issues that are causing the paste operation to only affect column F instead of the target range. Let's break down the problems and fix them step by step.

Key Issues in Your Original Code #

1. Uninitialized lastrow: You declared lastrow but never set it to the actual last row with data in column F. This meant your source range was invalid (or pointing to a single cell), leading to unexpected behavior.
2. Incorrect Target Range Selection: After copying, you reselected column F and tried to resize from there—this meant your target range still started in column F, so pasting just overwrote what you copied.
3. Overreliance on Select/Selection: Using these methods can lead to unstable code, especially if the user interacts with Excel while the macro runs.

Corrected Code #

Here's a revised version that achieves your goal reliably:

Sub CopyFormulasToRight()
    Dim lastrow As Long
    Dim lastcol As Long
    
    ' Get the last row with data in column F
    lastrow = Cells(Rows.Count, "F").End(xlUp).Row
    
    ' Get the last column with data in row 6 (as per your original logic)
    lastcol = Cells(6, Columns.Count).End(xlToLeft).Column
    
    ' Copy formulas from the source range (F2 to last row in F)
    Range("F2:F" & lastrow).Copy
    
    ' Paste formulas to the target range: starting at G2, extending to last column and last row
    Range("G2", Cells(lastrow, lastcol)).PasteSpecial Paste:=xlPasteFormulas
    
    ' Clear the clipboard to remove the "marching ants" selection
    Application.CutCopyMode = False
End Sub

How This Works #

• lastrow Initialization: Cells(Rows.Count, "F").End(xlUp).Row finds the bottom-most cell with data in column F, so we know exactly how far down to copy.
• Target Range Definition: Range("G2", Cells(lastrow, lastcol)) creates a range starting at the cell right of F2 (G2) and stretching to the last column (from row 6's data) and last row in column F.
• Direct Range References: We avoid Select/Selection entirely, making the code faster and less prone to errors.

Alternative: Even More Efficient (No Copy/Paste) #

If you want to skip the copy/paste step entirely (which is faster for large datasets), you can directly assign the formulas:

Sub AssignFormulasDirectly()
    Dim lastrow As Long
    Dim lastcol As Long
    
    lastrow = Cells(Rows.Count, "F").End(xlUp).Row
    lastcol = Cells(6, Columns.Count).End(xlToLeft).Column
    
    ' Assign formulas from column F to all columns to the right
    Range("G2", Cells(lastrow, lastcol)).Formula = Range("F2:F" & lastrow).Formula
End Sub

This method writes the formulas directly to the target cells without using the clipboard, which is more efficient for large ranges.

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