Excel VBA Copy and Paste Cells: Examples and Options

To copy a cell with Excel VBA, use the source range’s Copy method and specify a destination: sourceCell.Copy Destination:=destinationCell. This copies the cell’s contents and formatting; for values only, assign the source cell’s Value to the destination instead.

How VBA cell copying works

In VBA, a cell is a one-cell Range object, and a group of cells is also a range. The Copy method can place a copy on Excel’s clipboard or copy directly to another range. Supplying the optional Destination argument is usually the clearest approach for a macro because it states both ends of the operation in one line.

For example, copying A1 to B1 on the same worksheet can be written as Range("A1").Copy Destination:=Range("B1"). In a larger macro, it is safer to qualify both ranges with their worksheet, so the result does not depend on which sheet happens to be active. A fully qualified reference identifies the workbook, worksheet, and range explicitly.

A normal copy transfers more than the displayed text. Depending on the source cell, it can bring over its formula, number format, font, fill, borders, comments or notes, and other cell properties. The exact goal matters: copying a cell as a complete spreadsheet cell is different from transferring only its value or formula.

Copy one cell to another

This example copies A1 to B1 on a worksheet named Sheet1. Change the worksheet and cell references to match your workbook. The macro uses worksheet variables so that both source and destination are unambiguous.

Save a separate copy of the workbook before running a macro. VBA changes generally cannot be reversed with Ctrl+Z, so a backup is the reliable way to recover the original data if a macro does something unexpected.

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

    ' Identify the worksheet and the two cells.
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    Set sourceCell = ws.Range("A1")
    Set destinationCell = ws.Range("B1")

    ' Copy the cell, including its contents and formatting.
    sourceCell.Copy Destination:=destinationCell
End Sub

ThisWorkbook means the workbook containing the VBA code. That is often preferable to ActiveWorkbook, which means whichever workbook is currently active and could change if the macro opens or activates another file. If the source and destination belong to different worksheets, create a variable for each worksheet and set each range from its appropriate sheet.

Copy a range or repeat a cell

The same method works for a block of cells. If A2:C10 contains the source data, copy it to E2 by using the full source range and the top-left destination cell. Excel places the copied block starting at E2, maintaining its three-column by nine-row shape. The destination area should be large enough for the whole copied range; any existing contents in that area may be overwritten.

Sub CopyBlock()
    Dim ws As Worksheet
    Dim sourceRange As Range
    Dim destinationCell As Range

    ' Set the source block and the block's top-left destination.
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    Set sourceRange = ws.Range("A2:C10")
    Set destinationCell = ws.Range("E2")

    ' Copy the full block to the destination.
    sourceRange.Copy Destination:=destinationCell
End Sub

To fill several destination cells with the same value, a cell copy is not always the best fit. For a constant, assigning a value to a destination range can be simpler; for formulas that should adjust relative references across a region, Excel’s fill operations or copying a formula into the range may be more appropriate. Select the method based on whether the cells need identical values, adjusted formulas, or complete formatting.

Copy values without formulas or formatting

When a source cell contains a formula, a normal copy carries the formula, not just the result currently shown on screen. If the destination should contain only that evaluated result, use a direct value assignment. This does not use the clipboard and does not bring across the source formatting.

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

    ' Set the source and destination cells.
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    Set sourceCell = ws.Range("A1")
    Set destinationCell = ws.Range("B1")

    ' Transfer the current value, not the formula or formatting.
    destinationCell.Value = sourceCell.Value
End Sub

If A1 contains =SUM(C1:C3), the destination receives the calculated result rather than a formula. Dates and currency are stored as numeric values in Excel, so a value-only transfer may show a date serial or an unformatted number if the destination does not already have a suitable number format. Apply a destination format separately if the display needs to match.

For a block of values, assign one range’s values to another range of the same dimensions, for example Range("E2:G10").Value = Range("A2:C10").Value, with both ranges on the intended worksheet. Matching dimensions avoids ambiguous results. This assignment is often faster than clipboard copying for large blocks, but it does not copy formatting, column widths, or other cell features.

Copy formulas, formats, or selected parts

A normal copy is useful when the destination should behave like a copied cell, including formula behavior and formatting. Excel adjusts relative references as it would during a manual copy. For instance, if A1 contains a formula referring to B1, copying A1 to C1 normally shifts the relative reference in the copied formula. Absolute references marked with dollar signs, such as $B$1, remain fixed.

Directly assigning one cell’s Formula property to another is different: it transfers the formula text and does not perform the same relative-reference adjustment as a worksheet copy. That distinction can matter in calculated tables. Choose Copy Destination when you want Excel’s normal copy behavior; use a formula property assignment only when you deliberately want to set specific formula text.

To copy only values while using Excel’s Paste Special behavior, copy the source and then paste with the xlPasteValues option. This uses the clipboard, unlike direct value assignment. The macro below clears copy mode afterward, which removes the moving copy border and leaves Excel out of copy mode.

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

    ' Set the cells before using the clipboard.
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    Set sourceCell = ws.Range("A1")
    Set destinationCell = ws.Range("B1")

    ' Paste just the copied values, then clear copy mode.
    sourceCell.Copy
    destinationCell.PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
End Sub

Paste Special also supports other choices, including formulas and formats, through Excel’s built-in paste types. If only one property is required, use the corresponding paste type rather than copying everything and trying to remove unwanted parts afterward.

Choose the right copying method

These common methods serve different purposes:

Copy Destination:=: Use for a normal cell or range copy, including formulas and formatting, without selecting cells or relying on the clipboard sequence.
Value = Value: Use to transfer calculated values or constants without formulas and formatting. It is direct and usually convenient for data-only work.
Copy followed by PasteSpecial: Use when you need a specific part of a copied cell, such as values or formats, using Excel’s paste options.

One frequent misconception is that “copy a cell” always means “copy what I can see.” The displayed result may come from a formula, and the visible appearance may come from number formatting or conditional formatting. A standard copy can preserve underlying formulas and ordinary formatting, while value assignment preserves the evaluated value but not its display rules. Conditional formatting also depends on the destination’s rules and references; copying a cell does not guarantee that a rule will produce the same visible result in a different location.

Common errors and practical safeguards

A common runtime error occurs when code refers to a worksheet name that does not exist in the workbook. Worksheet names must match the tab names, including spaces. Another common issue is using an unqualified Range("A1") reference. That refers to the active worksheet, which may not be the intended one. Qualifying ranges with a worksheet variable helps prevent accidental copying from or to the wrong sheet.

Check these points when a copy does not produce the expected result:

Confirm that the source and destination worksheet names are correct and that the destination workbook is the intended one.
Check whether the destination range overlaps the source or contains information that should not be overwritten.
For a multi-cell copy, verify that the destination has enough rows and columns for the source block.
If using PasteSpecial, make sure a copy operation has occurred first; the clipboard-based approach depends on that sequence.
If formulas show unexpected references, decide whether you need normal copy adjustment or an exact formula assignment.

Merged cells can complicate block operations because Excel expects compatible merged-area shapes at the source and destination. Filtered lists and hidden rows can also make a copy appear different from what a reader expects, particularly when the intended operation is to copy visible cells only. If a macro must operate on visible cells or preserve a specific filtered layout, handle that requirement explicitly instead of assuming a basic range copy will infer it.

Run and save an Excel VBA macro

In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11, insert a standard module through Insert > Module, and place the procedure in that module. Run the macro from the editor with the cursor inside the procedure, or use Excel’s Developer tab and Macros command. If the Developer tab is not visible, enable it through File > Options > Customize Ribbon.

On a Mac, the Visual Basic Editor is available from the Developer tab or the Tools > Macro menu; keyboard behavior can depend on the keyboard’s function-key settings. Excel for the web can open a workbook containing VBA, but it cannot create, run, or edit VBA macros. Use the desktop Excel application for these procedures.

Save a workbook containing VBA in a macro-enabled format such as .xlsm; saving as a standard .xlsx file removes VBA code. Only enable macros in files you trust, since a macro can modify workbook data and perform other actions. Keep a backup before running unfamiliar code, especially when a destination range may contain valuable information.

Frequently Asked Questions

How do I copy a cell in Excel VBA without selecting it?

Reference the source and destination ranges directly and use sourceCell.Copy Destination:=destinationCell. The macro does not need to activate the worksheet or select either cell.

How do I copy only the displayed value of a formula?

Assign the source cell’s Value to the destination cell’s Value. This transfers the current calculated result rather than the formula, but it does not copy number formatting.

Does VBA copy formulas and formatting?

A normal Copy Destination:= operation copies the cell in the usual Excel manner, including its formula and formatting. Relative formula references adjust for the destination, while absolute references remain fixed.

Can I copy cells between worksheets?

Yes. Set the source range from one worksheet and the destination range from another, then use Copy Destination:=. Qualify both ranges so the macro does not depend on the active sheet.

Can I undo a VBA cell copy with Ctrl+Z?

Usually not. Running a VBA macro clears Excel’s normal undo history, so Ctrl+Z is not a reliable way to reverse its changes. Save a backup copy before running the macro.

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.