How to Copy and Paste Formulas with VBA in Excel Safely

To copy a formula with VBA in Excel, copy its source cell and use PasteSpecial Paste:=xlPasteFormulas on the destination range. This transfers formulas without copying the source cell’s formatting. For a repeatable fill-down that adjusts relative references, you can also assign the source formula’s FormulaR1C1 property to the full destination range.

The steps below are for Microsoft Excel for Microsoft 365 on Windows. Before running any macro, save a separate copy of your workbook: VBA changes cannot be reversed with Ctrl+Z.

What You’ll Need

Excel for Microsoft 365 desktop, or another desktop version of Excel that supports VBA. Excel for the web can open a macro-enabled workbook but cannot create, edit, or run VBA macros.
A workbook with a formula cell and a destination range. The examples use a worksheet named Sales, a formula in C2, and destination cells below it. Change these references to match your workbook.
A saved backup of your workbook. If you want to keep the macro in the file, save it as an Excel Macro-Enabled Workbook (.xlsm).

Step-by-Step

Identify the source formula and destination. Confirm which cell contains the formula you want to copy and which cells should receive it. For example, with quantities in A2:A4, unit prices in B2:B4, and totals in C2:C4, put this formula in C2: =A2*B2. It returns 30 when A2 contains 3 and B2 contains 10. Copying it to C3 and C4 should adjust the references; if A3 is 4 and B3 is 12, C3 returns 48, and if A4 is 2 and B4 is 8, C4 returns 16.
Decide whether you need formulas only or a full cell copy. The macro below uses Paste Special to copy formulas only, leaving destination formatting in place. If you want to copy formatting and other cell content too, use a normal copy-and-paste approach instead. Choose this before running the macro because the type of paste determines what changes in the destination.
Save a backup and enable the Developer tab if needed. On Windows, go to File > Options > Customize Ribbon, select Developer, and choose OK. Save a copy before proceeding. The Developer tab is where you can open the VBA editor and run macros. Do not enable macros from an untrusted file just to follow these steps.
Open the VBA editor and add a module. Press Alt+F11 on Windows. In the editor, choose Insert > Module. A blank code window appears. If the editor is already open, select the workbook project in the Project Explorer before inserting the module so you add the code to the intended workbook.
Paste the formula-only macro. Change the worksheet name, source cell, and destination range if your layout differs. The example copies the formula in C2 to C3:C100, without replacing destination formatting.

Sub CopyFormulaOnly()
    Dim ws As Worksheet
    Dim sourceCell As Range
    Dim destinationRange As Range

    ' Use the worksheet that contains the formula.
    Set ws = ThisWorkbook.Worksheets("Sales")
    Set sourceCell = ws.Range("C2")
    Set destinationRange = ws.Range("C3:C100")

    ' Paste formulas only into the destination range.
    sourceCell.Copy
    destinationRange.PasteSpecial Paste:=xlPasteFormulas
    Application.CutCopyMode = False
End Sub

Check the code references before running it. The worksheet name must match the tab name exactly, including spaces. The source must contain the intended formula, and the destination should include only cells you are willing to change. Because the code uses ThisWorkbook, it targets the workbook that contains the macro. If you put the macro in Personal.xlsb, this will not automatically mean the workbook currently visible on screen.
Run the macro. Return to Excel and use Developer > Macros, select CopyFormulaOnly, and click Run. On Windows, you can also press Alt+F8 to open the Macro dialog. Check the destination cells immediately afterward. For the example above, C3 should refer to A3 and B3, not A2 and B2.
Use FormulaR1C1 when filling a formula-only range repeatedly. This alternative writes a relative formula pattern into every cell in the destination range. For the example layout above, the source in C2 is =A2*B2; its relative pattern is two columns to the left multiplied by one column to the left. The code below transfers that pattern to C2:C100, so each row uses its own A and B cells. It does not copy formatting.

Sub FillFormulaPattern()
    Dim ws As Worksheet
    Dim sourceCell As Range
    Dim destinationRange As Range

    ' Set the source and destination on the same worksheet.
    Set ws = ThisWorkbook.Worksheets("Sales")
    Set sourceCell = ws.Range("C2")
    Set destinationRange = ws.Range("C2:C100")

    ' Apply the source formula's relative-reference pattern.
    destinationRange.FormulaR1C1 = sourceCell.FormulaR1C1
End Sub

Use one method for a particular task rather than running both without checking the result. The first method is closer to copying and pasting in the Excel interface; the second is useful for applying a consistent formula pattern to a range without using the clipboard.

Common Mistakes

Using the wrong sheet name. A name such as Sales Data is not the same as Sales. If the name does not match an existing worksheet, VBA stops with an error instead of completing the paste.
Copying a value instead of a formula. A cell may display a calculated number while containing a hard-coded value. Click the source cell and check the formula bar before running the macro. Paste Special cannot turn a fixed number into a formula.
Expecting formatting to copy with formulas only. xlPasteFormulas is specifically for formulas. It does not bring across the source cell’s number format, fill, borders, or font. Apply formatting separately if needed.
Overwriting useful destination cells. The macro replaces formulas or other contents in its destination range. Inspect the range first, especially when using a large block such as C3:C1000.
Confusing relative and absolute references. A copied formula normally adjusts relative references, but a reference with dollar signs stays fixed in the relevant row or column. Check whether the source formula uses references such as $A$2 intentionally.
Expecting Ctrl+Z to undo a macro. Excel clears its normal undo history when VBA changes cells. Restore the saved backup or run a carefully prepared recovery macro instead of relying on Undo.

Tips

Keep source and destination on the same worksheet when possible. The example explicitly sets both ranges from the ws variable, making the target clear and avoiding accidental pastes into whichever sheet happens to be active.
Use a bounded destination. Specify the exact rows needed, rather than copying to an entire column, unless the workbook design calls for that. Large ranges can take longer and may overwrite data you did not intend to change.
Choose the method based on what must be preserved. Use Paste Special for formula-only copying. Use FormulaR1C1 to apply the same relative formula pattern to a block. Use a normal copy when you also want the source’s formatting or other cell properties.
Make your macro easy to maintain. Put the worksheet name and range references near the top of the procedure, and add comments describing what is safe to change. If you reuse the macro, update those references deliberately instead of relying on the active cell.
Check a few results, not just whether the macro ran. Select the first, middle, and last destination cells and inspect their formulas in the formula bar. This can reveal a shifted range or an unexpected fixed reference before you use the results elsewhere.

Troubleshooting

“Subscript out of range” appears. The worksheet name in the macro probably does not exactly match a tab in the workbook containing the code. Check spelling and spaces, and confirm that ThisWorkbook is the workbook you intend to edit. If the macro is stored in a separate workbook, consider moving it into the target workbook or explicitly setting the correct workbook in the code.

The macro does not appear in the Macro dialog. Confirm that the code is in a standard module, not inside a worksheet or workbook object, and that the procedure is a public, parameter-free Sub. Also check that the file is saved in a format that retains macros and that Excel has not blocked macros under its security settings.

The destination cells show the same references as the source. Check whether the formula uses absolute references, such as $A$2, which are designed not to shift. If you want each row to use its own inputs, verify the source formula’s references and use the FormulaR1C1 pattern method where appropriate. Do not remove dollar signs automatically; they may be intentional.

The formula is copied but the results look wrong. Check the formula bar in the destination, then verify the referenced input cells and calculation settings. A formula can be copied correctly but still return an unexpected result because its inputs are blank, text, or different from what you assumed. Confirm a few rows manually before using the filled range.

Excel reports a clipboard or Paste Special error. Make sure the destination is a normal, editable range on the same worksheet, and that the sheet is not protected in a way that blocks edits. Close any dialog that may be interrupting Excel, then try the macro again from a saved backup. If clipboard copying remains unreliable, try the FormulaR1C1 method for a consistent relative formula pattern.

You are using Excel for Mac or the web. Excel for Mac supports VBA, but keyboard keys and settings screens can differ. Depending on the keyboard, use Option+F11 or Fn+Option+F11 to open the editor; you can also use the Developer tab. Enable that tab through Excel > Settings (or Preferences) > Ribbon & Toolbar. The web version cannot run VBA, so open the workbook in the desktop app.

Frequently Asked Questions

Does VBA copy formulas with their relative references adjusted?
Yes. Copying a formula cell to another location normally adjusts relative references, just as copying in the Excel interface does. Absolute references marked with dollar signs remain fixed.

How do I paste formulas without formatting?
Copy the source cell and use PasteSpecial Paste:=xlPasteFormulas on the destination range. The first macro in this guide does that; existing destination formatting is not replaced by the source formatting.

What is the difference between Formula and FormulaR1C1?
Formula uses A1-style references, while FormulaR1C1 expresses references relative to the formula cell using row and column offsets. Assigning a source cell’s FormulaR1C1 pattern across a range is a convenient way to fill a consistent formula down or across.

Can I copy a formula to another worksheet?
Yes. Set a separate worksheet variable for the destination and paste into its range. Confirm that the formula’s references are appropriate for the new sheet; copying between sheets can change references or leave formulas pointing to the original sheet, depending on the formula.

Can I undo a formula-copy macro?
Usually not with Ctrl+Z. VBA edits clear Excel’s regular undo history. Save a backup before running the macro, and check the destination range and workbook before saving over your original file.

Will the macro work in Excel for the web?
No. Excel for the web does not run VBA macros. Open the file in desktop Excel for Windows or Mac to edit or run the VBA procedure.

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.