Excel VBA Range Copy: Examples, Fixes, and Worksheet Tips

To copy cells with Excel VBA, use the Range.Copy method and specify a destination range; this copies the cells’ contents and formatting without requiring a separate paste step. For example, copy A1:D20 to A1 on another worksheet with sourceSheet.Range("A1:D20").Copy Destination:=targetSheet.Range("A1").

How Range.Copy works

A Range is a cell or group of cells identified by an address, such as A1 or A1:D20. The Copy method places a copy of that range either at a specified destination or on Excel’s clipboard. For a straightforward copy within the same workbook, supplying the Destination argument is usually the clearest approach:

Sub CopyRangeToArchive()
    Dim sourceSheet As Worksheet
    Dim targetSheet As Worksheet

    ' Identify the worksheets in this workbook.
    Set sourceSheet = ThisWorkbook.Worksheets("Data")
    Set targetSheet = ThisWorkbook.Worksheets("Archive")

    ' Copy the source cells, including their cell content and formatting.
    sourceSheet.Range("A1:D20").Copy Destination:=targetSheet.Range("A1")
End Sub

This example expects worksheets named Data and Archive in the workbook containing the macro. The destination argument identifies the upper-left cell where the copied block should begin. Excel fills the destination area with the copied range; it is not necessary to specify the entire destination block.

Copying is different from moving. The original cells remain in place, and the copied cells are separate cells that can be edited independently. Also, copying a cell formula generally adjusts relative references for the new location, just as it does when you copy and paste with Excel’s interface. Absolute references, marked with dollar signs, remain fixed as appropriate.

Copying between worksheets or workbooks

Use fully qualified worksheet references when the source and destination are on different sheets. In VBA, a worksheet name alone can be ambiguous: it could refer to a sheet in the active workbook rather than the one intended. Assigning each sheet to a Worksheet variable, as in the example above, makes the target explicit. ThisWorkbook means the workbook that contains the running macro; ActiveWorkbook means the workbook currently active in Excel. Those can be different, so choose deliberately.

To copy between two open workbooks, qualify each worksheet through its workbook object. The destination workbook must be open, and both named worksheets must exist. The pattern is:

Sub CopyBetweenOpenWorkbooks()
    Dim sourceSheet As Worksheet
    Dim targetSheet As Worksheet

    ' These workbook names must match the open workbook window names.
    Set sourceSheet = Workbooks("Source.xlsx").Worksheets("Data")
    Set targetSheet = Workbooks("Report.xlsx").Worksheets("Import")

    ' Copy the range to the upper-left destination cell.
    sourceSheet.Range("A1:D20").Copy Destination:=targetSheet.Range("A1")
End Sub

Use the actual workbook names shown in Excel, including file extensions when present. If a workbook is not open, the Workbooks collection cannot find it; opening it first or using a different file-handling approach is necessary. When the macro is stored in a workbook that should receive the copy, ThisWorkbook is often safer than relying on whichever workbook happens to be active.

Copying values, formulas, or formatting only

Range.Copy with a destination copies the cell range as a whole rather than limiting the operation to values. If the requirement is narrower, choose a method that expresses that requirement directly.

Values only: Assign the source range’s Value or Value2 property to a same-sized destination. This avoids the clipboard and does not copy formatting or formulas as formulas. Formula cells contribute their current calculated results.
Formulas only: Assign Formula to the destination when formulas should be transferred without copying the source formatting. References can behave differently from a normal copy-and-paste operation, so check relative-reference behavior for the specific ranges.
Formats only or another paste option: Use Copy followed by PasteSpecial with the relevant Excel constant, such as xlPasteFormats or xlPasteValues.

For example, this macro copies displayed calculation results as underlying values. Resize makes the target exactly as many rows and columns as the source:

Sub CopyValuesOnly()
    Dim sourceRange As Range
    Dim targetCell As Range
    Dim targetRange As Range

    Set sourceRange = ThisWorkbook.Worksheets("Data").Range("A1:D20")
    Set targetCell = ThisWorkbook.Worksheets("Archive").Range("A1")
    Set targetRange = targetCell.Resize(sourceRange.Rows.Count, sourceRange.Columns.Count)

    ' Assign values directly; formatting is not copied.
    targetRange.Value = sourceRange.Value
End Sub

Direct assignment is useful for transferring data inside a workbook without leaving a copy outline or changing Excel’s clipboard state. It does not make the destination look like the source. If dates, number formats, colors, or borders matter, copy the formats separately or use Copy and PasteSpecial.

Using PasteSpecial for selective copying

When you need Excel’s paste options, omit the Destination argument, call Copy, and then call PasteSpecial on the destination range. For example, this procedure pastes only values:

Sub PasteValuesOnly()
    Dim sourceRange As Range
    Dim targetCell As Range

    Set sourceRange = ThisWorkbook.Worksheets("Data").Range("A1:D20")
    Set targetCell = ThisWorkbook.Worksheets("Archive").Range("A1")

    ' Use the clipboard so Excel can apply a specific paste type.
    sourceRange.Copy
    targetCell.PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
End Sub

PasteSpecial supports other paste types, including formats and formulas. The paste type is selected with Excel’s built-in constants, for example xlPasteFormats or xlPasteFormulas. After a clipboard-based copy, setting Application.CutCopyMode to False clears the moving border and ends copy mode. It does not undo the copy; it simply clears the pending clipboard operation inside Excel.

For values plus number formats, use the appropriate PasteSpecial options or perform separate paste operations. Do not assume that “values” includes a source cell’s number format: a numeric date copied as values may arrive as the same serial number but display differently if the destination has a different format.

Choosing the right copying method

The best method depends on what the destination needs to contain, not just on how many cells are involved. Use this guide to choose:

Choose Copy Destination:=... for a normal cell copy that should include content and formatting.
Choose direct Value or Value2 assignment when you want results rather than formulas, do not need formatting, and want to avoid clipboard operations.
Choose PasteSpecial when you need a particular subset, such as formats, formulas, or values.
Choose a worksheet-to-worksheet assignment with explicit worksheet variables when the active sheet could change or the macro will be reused.

These approaches are not interchangeable in every situation. For example, direct values assignment will not carry over column widths, borders, fills, or data validation. A regular cell copy is more comprehensive, but may carry details you did not intend to reproduce. For a repeatable report, it is often clearer to copy the required values and then apply the specific formats the report needs.

Range sizes, offsets, and destination layout

The destination for a copied block is normally its upper-left cell. If the source range has four columns and twenty rows, Excel places that block starting at the destination cell. Check that the destination area is not occupied by information that must be retained: copying over existing cells replaces their contents and formatting as applicable. A copy does not automatically insert new worksheet rows or shift existing cells out of the way.

For a destination that should begin a fixed number of rows or columns from another cell, use Offset to identify its starting point. For a destination whose dimensions should match a source range, Resize is useful, particularly with direct value assignment. The source and target dimensions must be compatible for array-style assignment. A common mistake is assigning a multi-cell source to a single destination cell without resizing; define a target range with matching row and column counts instead.

If the number of rows varies, determine the correct last row before defining the source range. For example, a fixed address such as A1:D20 is appropriate only when the data really occupies that fixed block. Dynamic ranges need a reliable way to identify their boundaries; otherwise, the macro can omit new rows or include blank ones. Consider whether a formatted Excel Table is a better source when records are routinely added.

Common errors and how to prevent them

Most Range.Copy failures come from an incorrect reference or an assumption about the destination. Check these points when a macro raises an error or copies somewhere unexpected:

Worksheet not found: Confirm the tab name exactly, including spaces, and make sure the sheet belongs to the workbook object being used.
Workbook not open: A reference through Workbooks requires that workbook to be open. The displayed workbook name must match the name used in the code.
Wrong active sheet: Avoid unqualified Range references in reusable macros. Qualify the range through its Worksheet variable rather than relying on the active sheet.
Destination is protected: A protected sheet may prevent writing to locked cells. Review the sheet’s protection settings or use an appropriately designed macro; do not remove protection without authorization.
Overwritten data: Copying onto an occupied destination can replace existing information. Check the target area or add logic to stop if it is not empty.
Unexpected formula references: Relative references adjust when copied. Use absolute references in the original formula where needed, or explicitly decide whether formulas or calculated values should be transferred.
Clipboard remains active: After a Copy/PasteSpecial sequence, set Application.CutCopyMode to False when the copy operation is complete.

Also avoid selecting or activating ranges just to copy them. Directly qualifying the source and destination is generally easier to follow and less dependent on the user’s current selection. If a macro must work with filtered lists, merged cells, or multiple areas, test those cases specifically: copying a rectangular range and copying a noncontiguous selection are not the same operation.

Macro use, saving, and platform limits

Save a workbook containing VBA as an Excel Macro-Enabled Workbook (.xlsm), or use another macro-enabled format appropriate to the task. A standard .xlsx file does not retain VBA code when saved in that format. Excel may show a security warning when a file containing macros is opened; enable macros only when the file and its source are trusted. On Windows, macro settings are available through File > Options > Trust Center > Trust Center Settings > Macro Settings. Avoid lowering security settings just to run an unknown macro.

Save a copy of the workbook before running a macro that changes data. VBA changes cannot be reversed with Ctrl+Z, even if the macro only copies cells. Review the source and destination references, and test the procedure on a disposable copy first. VBA macros cannot run in Excel for the web; open the workbook in the desktop Excel application to run them. Excel for Mac supports VBA, but some Windows-specific features and integrations may differ. For copying ranges with the code shown here, use the desktop application and check any platform-specific dependencies in a larger macro.

Frequently Asked Questions

How do I copy a range to another sheet in VBA?

Set variables for the source and destination Worksheet objects, then call the source range’s Copy method with the target cell as Destination. This avoids relying on whichever sheet is active and copies the block beginning at that target cell.

Does Range.Copy copy formulas or values?

A regular range copy carries cell content, including formulas, along with formatting. Formulas generally adjust relative references for the new location. To transfer only calculated results, use direct value assignment or PasteSpecial with xlPasteValues.

How can I copy values without formatting?

Assign the source range’s Value or Value2 property to a destination range of matching dimensions. This transfers results without copying the source’s formatting. Alternatively, use Copy and PasteSpecial with xlPasteValues if you need the clipboard paste behavior.

Why does my copied formula change?

Excel adjusts relative cell references when formulas are copied, just as it does in a manual copy-and-paste. Use absolute references with dollar signs for references that should stay fixed, or transfer calculated values when the destination should not contain formulas.

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

No. Running a VBA macro clears Excel’s normal undo history, so Ctrl+Z cannot reverse the macro’s changes. Save a separate copy before running code that modifies a workbook, especially when the destination may contain existing data.

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.