Optimize Copy-Paste Formulas in VBA

To optimize formula copying in Excel VBA, assign a formula to an entire destination range at once instead of copying cells in a loop. Use FormulaR1C1 for formulas with relative references, and temporarily disable screen updating, events, and automatic calculation for large jobs—then restore their previous settings.

Why bulk formula assignment is faster

Excel VBA can spend much of its time communicating with the worksheet. A loop that reads or writes one cell at a time repeats that communication for every cell. Assigning a formula to a whole range makes the change in one operation, which is usually simpler and faster for thousands of rows.

For example, suppose column A contains quantities, column B contains unit prices, and column C should show each line total. With quantities in A2:A10001 and prices in B2:B10001, the formula in C2 is =A2*B2. If A2 is 3 and B2 is 12.5, the result is 37.5. The same relative-reference pattern can be written to the entire C range in one VBA assignment.

Copying and pasting is still useful when you need to carry formatting, validation, or other cell properties along with formulas. But if the goal is only to put formulas into cells, direct assignment avoids unnecessary clipboard work and makes the intended result clearer.

Use FormulaR1C1 for relative references

FormulaR1C1 describes a formula using row-and-column offsets rather than A1-style addresses. In the example above, each total is two columns to the left multiplied by one column to the left. The R1C1 expression is =RC[-2]*RC[-1]. Because those offsets are relative to each destination cell, one assignment creates the appropriate formula throughout column C.

For a normal A1-style formula, use the Formula property. For formulas that need Excel 365 dynamic-array behavior, use Formula2, which handles formula evaluation differently from the older compatibility-oriented Formula property. Choose the property that matches the formula and the Excel versions that will use the workbook.

With values in A2:A10001 and B2:B10001 and totals in C2:C10001, this macro writes the formula in one operation:

Sub FillCalculatedColumn()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim target As Range
    Dim oldCalculation As XlCalculation
    Dim oldScreenUpdating As Boolean
    Dim oldEnableEvents As Boolean
    Dim errorText As String

    On Error GoTo ErrHandler

    ' Preserve application settings before changing them.
    oldCalculation = Application.Calculation
    oldScreenUpdating = Application.ScreenUpdating
    oldEnableEvents = Application.EnableEvents

    Set ws = ThisWorkbook.Worksheets("Data")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    If lastRow < 2 Then GoTo CleanExit

    Set target = ws.Range("C2:C" & lastRow)

    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual

    ' Each C cell multiplies the values two and one columns to its left.
    target.FormulaR1C1 = "=RC[-2]*RC[-1]"

    GoTo CleanExit

ErrHandler:
    errorText = "The macro stopped: " & Err.Description
    Resume CleanExit

CleanExit:
    ' Restore the settings that were active before the macro ran.
    Application.ScreenUpdating = oldScreenUpdating
    Application.EnableEvents = oldEnableEvents
    Application.Calculation = oldCalculation

    If Len(errorText) > 0 Then MsgBox errorText, vbExclamation
End Sub

Change the worksheet name and columns to match your file. The final row in this example is found by moving upward from the bottom of column A, so it assumes that column A is populated for every data row you want to process. If a different column reliably marks the last row, use that column instead.

Choose between Formula, FormulaR1C1, and Copy

The right method depends on what should be transferred. Formula assignment is usually best when the macro is generating or replacing formulas. Copying is more suitable when the source cells contain formatting or other properties that must also be reproduced.

Use Formula when an A1-style formula is convenient and the target range is known. The formula should be appropriate for the destination cells.
Use FormulaR1C1 when a formula repeats by relative position, such as multiplying the two cells to the left or summing the cells above.
Use Formula2 for dynamic-array formulas when you need modern Excel formula semantics.
Use Copy with a destination when formulas and cell formatting should travel together. A destination argument avoids relying on a separate clipboard paste step.
Use Value2 when you want to transfer calculated values rather than formulas. It is also commonly used to replace formulas with their current results.

Copying a formula is not the same as copying its displayed result. A copied formula continues to calculate and may change when its inputs change. A value-only transfer is static until another macro or user changes it. Decide which behavior is required before choosing the method.

Turn off expensive application work carefully

For large operations, setting Application.ScreenUpdating to False prevents Excel from repeatedly repainting the screen. Setting Application.Calculation to xlCalculationManual can prevent formulas from recalculating after every change. Disabling Application.EnableEvents can avoid triggering worksheet or workbook event procedures during a bulk update.

These settings affect Excel while the macro runs, not just the target range. Save their original values and restore those exact values afterward, including when an error occurs. Do not simply set calculation back to automatic: the user may have started Excel in manual calculation mode. Also, disabling events is inappropriate if event macros are meant to respond to the changes. Manual calculation can leave displayed results out of date until calculation occurs, so make sure the workbook is recalculated when needed.

Screen updating alone may not make a large formula operation fast if calculation or event code is the real bottleneck. Conversely, changing global settings for a small range can add complexity without a meaningful benefit. Measure the task’s needs and keep the optimization proportional.

When copying or pasting is the better choice

If an existing cell is a carefully formatted template, copying it may be more reliable than rebuilding its number format, borders, and formula separately. In VBA, a copy with a destination is clearer than selecting a cell, copying it, activating another sheet, and pasting. Avoid Select and Activate unless the macro genuinely needs to interact with the active selection.

Use copying when the destination should inherit source formatting or when the exact formula pattern already exists. Use bulk formula assignment when you want to control the formula independently of formatting. If only values are required, copy-and-paste-values or a Value2 assignment prevents formulas from being carried into the destination.

For example, if a destination range already contains formulas and you need to freeze their current results, assigning the range’s values back to itself replaces formulas with their calculated values. Do this only when losing the formulas is intentional; the conversion is not a reversible substitute for keeping a copy of the original workbook.

Common mistakes and troubleshooting

Using the wrong reference style: A1 formulas and R1C1 formulas are different representations. A string such as =RC[-2]*RC[-1] belongs in a R1C1 assignment, not in a normal A1 formula assignment.
Filling the wrong number of rows: Check the column used to find the last row. If it has blanks within the data or a different endpoint from the other columns, the result may omit records or include extras.
Leaving calculation manual: If a macro stops before restoring application settings, formulas may appear stale. Use error handling and restore prior settings on every exit path.
Unexpected event behavior: Disabling events means event procedures do not run during the update. If those procedures maintain other parts of the workbook, either leave events enabled or deliberately perform the required work another way.
Overwriting existing content: A bulk assignment replaces every cell in its destination range. Confirm that the range is correct and that no unrelated data will be overwritten.
Assuming faster code is always safer: Disabling calculation or events changes workbook behavior during execution. These are performance choices, not harmless defaults.

Test and run the macro safely

Before using a macro on important data, save a separate copy of the workbook and test the code on a small sample. VBA macros cannot be undone with Ctrl+Z, so a backup is the practical way to recover from an accidental overwrite. Confirm the worksheet name, destination range, formula references, and expected results before expanding to the full dataset.

On Excel for Microsoft 365 on Windows, press Alt+F11 to open the Visual Basic Editor; Alt+F8 opens the Macro dialog. On Mac, Option+F11 may open the editor, and some keyboards require Fn as well; shortcut behavior can depend on macOS and keyboard settings. Excel for the web does not run VBA macros. Save a macro-enabled workbook as an .xlsm file, and only enable macros in files you trust.

Frequently Asked Questions

Is FormulaR1C1 faster than copying formulas?
For a large destination range, one bulk FormulaR1C1 assignment can avoid repeated cell-by-cell operations and clipboard handling. The actual speed depends on calculation, events, workbook size, and what else the copy would transfer.

How do I copy a formula down without changing its relative references incorrectly?
Use a relative-reference pattern with FormulaR1C1, such as =RC[-2]*RC[-1] for a formula in column C that multiplies columns A and B on the same row. Check the offsets from the destination column before applying it to the full range.

Should I turn calculation back to automatic after the macro?
Restore the calculation mode that was active before the macro started. That may be automatic, manual, or another Excel calculation mode; preserving the original state avoids changing the user’s setting.

Can VBA paste formulas without copying formatting?
Yes. Assign a formula through Formula or FormulaR1C1 to write formulas without copying the source cell’s formatting. Use Value2 when you want values rather than formulas.

Can I use this macro in Excel for the web?
No. Excel for the web does not execute VBA. Open the macro-enabled workbook in the desktop version of Excel to run VBA, or consider Office Scripts for supported web-based automation.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.