Excel VBA Copy and Paste Formulas Between Cells Safely

To copy a formula to another cell with Excel VBA, use Copy followed by PasteSpecial Paste:=xlPasteFormulas. This pastes the formula without copying the source cell’s formatting; Excel adjusts relative references as it does in a normal copy-and-paste operation.

Copy a Formula to One Cell

This example copies the formula in C2 on a worksheet named Data to C3. Change the worksheet name and cell addresses to suit your workbook. The destination cell receives the formula, but not the source cell’s fill, font, borders, or other formatting.

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

    Set ws = ThisWorkbook.Worksheets("Data")
    Set sourceCell = ws.Range("C2")
    Set destinationCell = ws.Range("C3")

    ' Copy the formula without copying cell formatting.
    sourceCell.Copy
    destinationCell.PasteSpecial Paste:=xlPasteFormulas
    Application.CutCopyMode = False
End Sub

In this example, ThisWorkbook means the workbook containing the macro. Qualifying the worksheet and ranges avoids relying on whichever sheet happens to be active when the code runs. The final line clears Excel’s moving copy border and ends copy mode; it is not required for the formula transfer itself, but it leaves the application in a tidier state.

How Formula Copying Changes Cell References

When Excel copies a formula, it normally updates relative references according to the move. For example, if C2 contains =A2*B2, copying it one row down to C3 produces =A3*B3. The source formula stays in C2; the destination receives a formula adapted to its new location.

Absolute and mixed references follow their usual rules. If the source formula is =$A$2*B2, copying it down keeps the reference to A2 fixed while adjusting the relative reference to column B’s next row. The dollar signs lock the relevant row or column. VBA’s paste-special method follows Excel’s regular copy behavior, so it is generally the clearest choice when you want a macro to behave like a person copying a formula.

Copying a formula is not the same as copying its displayed result. A formula that currently displays 12 remains a formula in the destination and can recalculate when its inputs change. To paste only the calculated result, use xlPasteValues instead; that is a different operation.

Paste Formulas Across a Range

For a block of formulas, copy a source range and paste it into a destination range of the same dimensions. For example, a two-column by ten-row source block should be pasted into a destination block that is also two columns by ten rows. Excel then adjusts relative references for each copied position.

Sub CopyFormulaBlock()
    Dim ws As Worksheet
    Dim sourceRange As Range
    Dim destinationRange As Range

    Set ws = ThisWorkbook.Worksheets("Data")
    Set sourceRange = ws.Range("C2:D11")
    Set destinationRange = ws.Range("F2:G11")

    ' Paste formulas only into a same-sized destination.
    sourceRange.Copy
    destinationRange.PasteSpecial Paste:=xlPasteFormulas
    Application.CutCopyMode = False
End Sub

A range can contain both formulas and constants. With this method, formula cells are pasted as formulas and constant cells are pasted as their values, while formatting is excluded. Check that the destination will not overwrite important information: a macro can replace existing contents without prompting in the way a manual edit might.

Use FormulaR1C1 for a Direct Assignment

If you are copying one formula cell and want to avoid the clipboard, assigning its FormulaR1C1 property is another option. It represents references relative to the formula cell, so the same reference pattern is transferred to the destination. For instance, a formula that multiplies the values two and one columns to the left keeps that relationship when placed in a different cell.

Sub AssignFormulaPattern()
    Dim ws As Worksheet
    Dim sourceCell As Range
    Dim destinationCell As Range

    Set ws = ThisWorkbook.Worksheets("Data")
    Set sourceCell = ws.Range("C2")
    Set destinationCell = ws.Range("C3")

    ' Transfer the formula's relative-reference pattern.
    destinationCell.FormulaR1C1 = sourceCell.FormulaR1C1
End Sub

This method does not perform a clipboard paste and does not copy formatting. It is useful when you want to assign a formula pattern directly. For a one-cell formula, it preserves the relationship between relative references and the formula cell, while absolute references remain absolute. If your goal is specifically to reproduce Excel’s normal copy-and-paste behavior across a selection, Copy with PasteSpecial is easier to recognize and maintain.

Fill a Formula Down to the Last Data Row

A common task is to extend a formula in C2 down alongside a list whose entries are in column A. The following macro finds the last non-empty cell in column A and assigns the formula pattern from C2 throughout column C. It assumes row 1 contains headings, row 2 contains the first data row and the formula, and column A has an entry for every row that should be filled.

Sub FillFormulaDown()
    Dim ws As Worksheet
    Dim lastRow As Long

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

    ' Stop if there is no data row to fill.
    If lastRow < 2 Then Exit Sub

    ' Reuse the relative-reference pattern in C2.
    ws.Range("C2:C" & lastRow).FormulaR1C1 = ws.Range("C2").FormulaR1C1
End Sub

The End(xlUp) approach identifies the last occupied cell by moving upward from the bottom of column A. If column A has gaps or does not reliably mark the end of the data, choose a different column or calculate the final row using the structure of your sheet. Also confirm that C2 really contains a formula before running the macro; otherwise, the code may fill the range with a value or blank rather than the calculation you intended.

Choose the Right VBA Method

There is no single best method for every formula-copying task. Pick the approach based on what should happen to formulas, formatting, and cell contents:

Formula only, with normal reference adjustment: use Copy and PasteSpecial Paste:=xlPasteFormulas.
Copy formulas and formatting together: use Copy Destination:= or a regular paste. These methods copy more than formulas, so they can replace destination formatting.
Assign a formula pattern without using the clipboard: use FormulaR1C1, especially for a single source formula or a controlled fill operation.
Keep the result but remove the formula: paste values, not formulas.

Directly assigning the source cell’s Formula property is not always equivalent to copying it. A formula string containing relative A1-style addresses can be placed unchanged at the destination, leaving references pointing at the original cells rather than shifting as expected. Use FormulaR1C1 for a relative pattern or use paste-special when you need ordinary copy behavior.

Common Problems and How to Avoid Them

The destination formula points to unexpected cells. Check whether the source formula uses relative, absolute, or mixed references. If you assigned a formula string directly, consider whether the references should move; use paste-special or a relative-reference pattern when they should.

The macro pastes formatting too. A regular copy can bring formats along. Use xlPasteFormulas when the destination should retain its existing number format, colors, borders, and other appearance. If you also need number formats, treat that as a separate requirement and use the appropriate paste type rather than assuming formula paste includes them.

The code fails on a worksheet name. The name in Worksheets("Data") must match the sheet tab exactly. A misspelling or a renamed tab can cause a run-time error. Explicitly qualify every range with its worksheet, particularly in macros that switch between sheets.

A spill formula cannot be copied into the intended area. Dynamic-array formulas may spill results into neighboring cells. Usually, the formula belongs in the top-left anchor cell; existing content in the spill area can block the result. Copying a formula does not make occupied spill cells safe to overwrite.

Undo does not restore the previous sheet. VBA actions generally clear Excel’s normal undo history, and macro changes cannot be undone with Ctrl+Z. Save a separate copy of the workbook before running a macro that changes data, especially when it fills or replaces a large range.

Run and Save the Macro Safely

In Excel for Microsoft 365 on Windows, press Alt+F11 to open the Visual Basic Editor, choose Insert > Module, and paste the code into the module. Return to Excel and run the macro from the editor or through Developer > Macros. If the Developer tab is hidden, enable it through File > Options > Customize Ribbon. Save a workbook containing VBA as an Excel Macro-Enabled Workbook (.xlsm); the standard .xlsx format does not retain VBA code.

Save a copy before running a macro because its changes cannot be reversed with Ctrl+Z. Only enable macros in workbooks from sources you trust; a macro can make changes beyond copying a formula. Excel for Mac supports VBA, but keyboard shortcuts and some Excel features may differ; on many Mac keyboards, Option+F11 opens the Visual Basic Editor, sometimes with the Fn key as well. Excel for the web can open a macro-enabled workbook, but it cannot create, edit, or run VBA macros.

Frequently Asked Questions

How do I copy a formula without copying formatting in VBA?
Copy the source range, then use PasteSpecial Paste:=xlPasteFormulas on the destination. This transfers formulas and leaves the destination’s formatting in place.

Does VBA adjust cell references when it copies a formula?
Yes, Copy followed by formula paste adjusts relative references as Excel normally does. Absolute references marked with dollar signs remain locked as specified by the formula.

How do I copy a formula down an entire column?
Use a source formula cell and fill a range ending at the last data row. Find that row from a dependable column, then use either formula paste or a FormulaR1C1 assignment to repeat the formula pattern.

What is the difference between Formula and FormulaR1C1?
Formula exposes a formula using A1-style references. FormulaR1C1 uses row-and-column-relative reference notation, which makes it useful for transferring the same relative formula pattern to another location without the clipboard.

Can I run Excel VBA in a browser?
No. Excel for the web can display and edit many workbook contents, but VBA macros must be run in the desktop Excel application. Use Excel for Windows or Mac to run the macro-enabled workbook.

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.