VBA Copy and Paste Cells: Examples and Tips

In Excel VBA, copy cells with the Copy method: specify a source range and a destination, such as sourceRange.Copy Destination:=destinationCell. To paste only values or formatting, copy the source and use PasteSpecial with the option you need.

The key choice is whether you want a complete cell copy, a selective paste, or just the calculated contents. The examples below are for Excel for Microsoft 365 on Windows and show how to copy between ranges and worksheets without relying on selecting cells first.

What VBA copies when you copy cells

A cell is more than its displayed text. It can contain a value, a formula, number formatting, fill and font formatting, borders, validation rules, and other properties. A normal range copy generally carries the cell contents and formatting to the destination. If formulas are included, Excel adjusts relative references to suit the new location, just as it does when you copy and paste cells manually.

For example, a formula in B2 that refers to A2 will normally adjust to refer to A3 if copied one row down. Absolute references, such as $A$2, remain fixed. This difference matters when copying calculated columns: a copied formula may be correct even though its text changes, while a formula with fixed references may intentionally keep pointing to the same cell.

VBA can also copy only one part of a cell. A values-only paste removes formulas from the destination and leaves their current results. A formats-only paste changes appearance but preserves the destination’s existing values and formulas. Choosing the right kind of paste helps avoid unintentionally replacing data.

Choose the right copy method

Use Copy Destination:= for a straightforward full copy. It names the destination directly, so there is no need to activate a worksheet or use the clipboard’s paste command. Use PasteSpecial when you need to control what is transferred, such as values, formulas, or formats. If you need only the underlying values and do not need Excel’s copy-and-paste behavior, direct assignment is often simpler.

Full cell copy: Use Copy Destination:= when contents and cell formatting should move together.
Values only: Use PasteSpecial Paste:=xlPasteValues to replace formulas with their current results.
Formats only: Use PasteSpecial Paste:=xlPasteFormats to copy appearance without overwriting destination contents.
Values by assignment: Set one same-sized range’s Value to another’s when you need values without clipboard operations.

For routine copying, avoiding Select and Activate makes a macro less dependent on what the user clicked last. It also makes the source and destination explicit, which is easier to review and maintain.

Copy a range to another location

This example copies A2:C10 on a worksheet named Data to A2 on a worksheet named Report in the workbook that contains the macro. The destination’s top-left cell determines where the copied block begins; Excel fills the corresponding rows and columns.

Option Explicit
Sub CopyRangeToReport()
    Dim sourceRange As Range
    Dim destinationCell As Range

    Set sourceRange = ThisWorkbook.Worksheets("Data").Range("A2:C10")
    Set destinationCell = ThisWorkbook.Worksheets("Report").Range("A2")

    sourceRange.Copy Destination:=destinationCell
End Sub

Replace the worksheet names and addresses with those in your file. ThisWorkbook means the workbook where the VBA code is stored; it does not necessarily mean the workbook currently active on screen. This is useful when a macro should always work with its own workbook, but it is worth checking if your code is stored in a personal macro workbook or add-in.

The copy will overwrite cells in the destination block. If Report!A2:C10 already contains information, that content can be replaced. Check the target range before running a macro, especially when the destination is a shared report or a sheet with formulas.

Paste values, formulas, or formatting only

For a selective paste, copy the source range first and then call PasteSpecial on the destination. The following example transfers the current results from Data!A2:C10 to Report!A2:C10, rather than transferring the formulas that may have produced those results.

Option Explicit
Sub PasteValuesOnly()
    Dim sourceRange As Range
    Dim destinationCell As Range

    Set sourceRange = ThisWorkbook.Worksheets("Data").Range("A2:C10")
    Set destinationCell = ThisWorkbook.Worksheets("Report").Range("A2")

    sourceRange.Copy
    destinationCell.PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
End Sub

To paste formats instead, change the paste option to xlPasteFormats. To paste formulas only, use xlPasteFormulas. These options do not all transfer the same properties: for example, formats concern appearance, while formulas concern the calculation expression. If the destination also needs column widths, that is a separate paste choice, xlPasteColumnWidths; it does not mean the destination’s other cell contents are copied.

When copying values between ranges of the same dimensions, direct assignment avoids the clipboard. With values in the source range, the core operation is destinationRange.Value = sourceRange.Value. This is useful for data-transfer macros, but it does not copy formatting, validation, or formulas as formulas. The destination receives the source’s current values.

Copy changing data and append it below existing rows

A fixed address is appropriate when the data size never changes. For a list that grows, find its last used row and build the source range at run time. This example treats column A as the indicator of a populated record, copies columns A:C starting at row 2, and appends those records to the next available row of a worksheet named Archive.

Option Explicit
Sub AppendDataToArchive()
    Dim sourceSheet As Worksheet
    Dim targetSheet As Worksheet
    Dim lastSourceRow As Long
    Dim nextTargetRow As Long

    Set sourceSheet = ThisWorkbook.Worksheets("Data")
    Set targetSheet = ThisWorkbook.Worksheets("Archive")

    lastSourceRow = sourceSheet.Cells(sourceSheet.Rows.Count, "A").End(xlUp).Row
    If lastSourceRow < 2 Then Exit Sub

    nextTargetRow = targetSheet.Cells(targetSheet.Rows.Count, "A").End(xlUp).Row + 1
    sourceSheet.Range("A2:C" & lastSourceRow).Copy _
        Destination:=targetSheet.Range("A" & nextTargetRow)
End Sub

The test for a last source row below 2 prevents the macro from trying to append when there are no data rows under the assumed header. The method depends on column A being filled for each record. If records can have a blank in that column, choose a column that is reliably populated or use a different way to identify the data boundary. Also check whether Archive has a header: this code appends after the last nonblank cell in column A and does not create or copy a header itself.

Common problems and how to avoid them

“Subscript out of range”: A worksheet name in the code may not match the tab name exactly, or the named sheet may be in a different workbook. Confirm spelling and which workbook the code refers to.
Unexpected overwriting: The destination starts at the cell you specify and expands to fit the source range. Make sure the resulting block is clear or that replacing it is intended.
Formulas change after copying: Excel adjusts relative references. Use absolute references in the worksheet formula where appropriate, or paste values if the destination should contain results rather than formulas.
Only some data seems to copy: A values-only paste does not bring over formulas as formulas or formatting. Check that the chosen paste type matches the intended result.
The macro depends on the active sheet: Unqualified references such as Range("A1") can point to the active worksheet. Qualify ranges with a worksheet variable or ThisWorkbook.Worksheets("Data").

Clipboard-based copying can leave Excel in copy mode, often shown by a moving border around the source cells. Setting Application.CutCopyMode = False after a paste clears that state. It is useful after a completed copy-and-paste operation, but not if you still intend to paste again.

Run a macro safely

In Excel for Microsoft 365 on Windows, open the Visual Basic Editor from Developer > Visual Basic, insert a standard module, and put the procedure there. If the Developer tab is hidden, enable it through File > Options > Customize Ribbon by checking Developer. You can run a procedure from the editor or use Developer > Macros, select its name, and choose Run.

Save a copy of the workbook before running a macro. VBA actions cannot be undone with Ctrl+Z.
Save a workbook containing VBA as an Excel Macro-Enabled Workbook (.xlsm) if you want the code to remain in that file.
Review the source and destination sheet names, ranges, and overwrite behavior before execution.
Run the macro on a copy first if the workbook contains important or difficult-to-recreate data.

On Mac, VBA is available in desktop Excel, though the ribbon and editor access can differ from Windows. Use the Developer tab or Excel’s Tools > Macro menu to reach macro commands, depending on the interface version. Windows keyboard shortcuts such as Alt+F11 and Ctrl+C/Ctrl+V do not map identically to Mac: Command+C and Command+V are the usual Mac copy and paste shortcuts, while opening the VBA editor can depend on the keyboard’s function-key settings. The examples’ VBA methods are generally the same in desktop Excel on both platforms.

Macros may be blocked by security settings, and files from the internet can open with macros disabled. Enable code only when you trust its source and understand what it does; do not lower macro security globally just to run an unfamiliar file. Excel for the web does not run VBA macros, so use desktop Excel to execute these examples.

Frequently Asked Questions

How do I copy a cell in VBA without using Select?
Set a Range variable for the source and use sourceRange.Copy Destination:=destinationCell. Fully qualifying the worksheet and workbook makes the target clear and prevents the macro from depending on the active cell or sheet.

How do I paste values only in VBA?
Use sourceRange.Copy, followed by destinationCell.PasteSpecial Paste:=xlPasteValues. If source and destination ranges are the same size and you only need their values, direct Value assignment is another option.

Does VBA copy formulas and formatting too?
A normal range copy generally copies formulas, values, and formatting. Formulas adjust according to relative and absolute references. Use a specific PasteSpecial option when you want only values, formulas, or formats.

Can VBA copy cells to another worksheet?
Yes. Reference each worksheet explicitly, for example ThisWorkbook.Worksheets("Data") for the source and ThisWorkbook.Worksheets("Report") for the destination. Both sheets need to exist in the workbook identified by ThisWorkbook.

Why does my copied formula show a different reference?
Excel adjusts relative references when a formula moves, which is expected copy behavior. Use absolute references such as $A$2 when a reference must stay fixed, or paste values when you want the calculated result instead of a formula.

Can I undo a VBA paste with Ctrl+Z?
No. Running a VBA macro clears Excel’s normal undo history, so Ctrl+Z cannot reliably reverse its changes. Save a separate copy before running code that writes to cells.

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.