VBA Excel: Copy a Range of Cells

To copy a range of cells in VBA, use the range’s Copy method and specify the top-left destination cell: sourceRange.Copy Destination:=targetSheet.Range("A1"). This copies cell contents and formatting without needing to select the cells first; use a value assignment instead if you want to transfer values only.

How VBA range copying works

Excel represents a group of cells as a Range object. You can identify that object by an address such as Range("A1:C10"), or store it in a variable. Calling Copy on the range copies it to the Clipboard unless you supply a destination. With Destination:=, Excel places the copied range starting at the cell you name.

The destination argument is the top-left cell, not a range that must match the source dimensions. For example, copying A1:C10 to E1 fills E1:G10. The source and destination do not need to be on the same worksheet, but both sheets must be available and the destination sheet must not be protected against the changes the macro is making.

One useful feature of the regular copy operation is that Excel adjusts relative formula references for the new position, much as it does when you copy and paste manually. A reference marked with dollar signs, such as $A$1, remains absolute. The destination receives the copied cells’ formulas or values and their cell formatting. Copying a range does not automatically copy the source columns’ widths, and it is not a general way to duplicate worksheet objects such as charts or shapes.

A complete macro for copying cells

This example copies A1:C10 from a worksheet named Data to the top-left corner of a worksheet named Archive in the workbook that contains the macro. Replace the sheet names and addresses with the ones in your workbook. The named worksheets must exist in that workbook.

Sub CopyRangeToArchive()
    Dim wsSource As Worksheet
    Dim wsTarget As Worksheet
    Dim sourceRange As Range

    ' Set the worksheets and the cells to copy.
    Set wsSource = ThisWorkbook.Worksheets("Data")
    Set wsTarget = ThisWorkbook.Worksheets("Archive")
    Set sourceRange = wsSource.Range("A1:C10")

    ' Copy the range to the target's top-left cell.
    sourceRange.Copy Destination:=wsTarget.Range("A1")
End Sub

The worksheet variables make the references explicit. That matters because an unqualified expression such as Range("A1:C10") refers to the active worksheet, which may not be the sheet you intended. The code does not use Select or Activate; those steps are usually unnecessary and can make a macro depend on which sheet happens to be active.

Before running a macro, save a copy of the workbook. VBA changes cannot be undone with Ctrl+Z, so a backup is the simplest way to recover from an unwanted copy or overwrite. Save a workbook containing VBA as an Excel Macro-Enabled Workbook (.xlsm) if it needs to retain the macro.

Choose between copying everything and values only

Use Copy Destination:= when the goal is to reproduce cells in the usual Excel sense. It carries formulas as formulas, preserves their relative-reference behavior, and copies cell formatting. That is appropriate for moving a formatted report block, duplicating a template area, or placing a formula-driven table on another sheet.

If you want only the calculated values, assign the source range’s Value property to the destination. This avoids copying formulas and does not replace the destination’s existing formatting. The source and destination ranges should have the same dimensions:

Sub CopyValuesOnly()
    Dim wsSource As Worksheet
    Dim wsTarget As Worksheet
    Dim sourceRange As Range
    Dim targetRange As Range

    ' Set equal-sized source and destination ranges.
    Set wsSource = ThisWorkbook.Worksheets("Data")
    Set wsTarget = ThisWorkbook.Worksheets("Archive")
    Set sourceRange = wsSource.Range("A1:C10")
    Set targetRange = wsTarget.Range("A1:C10")

    ' Transfer values, not formulas or source formatting.
    targetRange.Value = sourceRange.Value
End Sub

For example, if a source cell contains =B2*C2, the values-only method writes its current calculated result to the target rather than copying that formula. Dates and times are stored as numbers in Excel, so the assignment transfers their underlying values; the target may need an appropriate number format to display them as dates or times. Error values can also be transferred as values.

There is a distinction between copying formulas and assigning the Formula property. A normal range copy adjusts relative references to the new location. Assigning one range’s formula property to another can carry the same formula text instead, so references may not shift as they would with a standard copy. Choose the method based on whether you need Excel’s normal copy behavior or a deliberate formula assignment.

Copy between worksheets or open workbooks

For sheets in the same workbook, qualify both the source and destination with their worksheet variables, as in the complete example above. If you omit the workbook, ThisWorkbook means the workbook containing the VBA code. This is different from ActiveWorkbook, which means the workbook currently active in Excel. That distinction matters when a macro is stored in a separate personal macro workbook or add-in.

To copy between two already-open workbooks, qualify each worksheet through its workbook. Workbook names in the Workbooks collection include the file extension, and the names must match the open workbooks:

Sub CopyBetweenOpenWorkbooks()
    Dim wbSource As Workbook
    Dim wbTarget As Workbook
    Dim wsSource As Worksheet
    Dim wsTarget As Worksheet

    ' Both workbooks must already be open in Excel.
    Set wbSource = Workbooks("Sales.xlsx")
    Set wbTarget = Workbooks("Report.xlsx")
    Set wsSource = wbSource.Worksheets("Data")
    Set wsTarget = wbTarget.Worksheets("Summary")

    ' Copy cells to the destination worksheet.
    wsSource.Range("A1:D20").Copy Destination:=wsTarget.Range("A1")
End Sub

If a workbook is not open, this example cannot find it. You would need to open the file in VBA before referring to it, and decide whether the macro should save changes to that workbook. When automating files, verify the intended file and destination before running the code; a correctly written copy command can still overwrite useful cells.

Copy a range whose size changes

When new records are added regularly, a fixed address such as A1:C10 may omit later rows. A common approach is to find the last used row in a key column and build the source range from that result. The following example assumes row 1 contains headers, records begin in row 2, and column A is populated for every record:

Sub CopyCurrentData()
    Dim wsSource As Worksheet
    Dim wsTarget As Worksheet
    Dim lastRow As Long
    Dim sourceRange As Range

    ' Find the last non-empty cell in column A.
    Set wsSource = ThisWorkbook.Worksheets("Data")
    Set wsTarget = ThisWorkbook.Worksheets("Archive")
    lastRow = wsSource.Cells(wsSource.Rows.Count, "A").End(xlUp).Row

    ' Stop if there are no data rows below the header.
    If lastRow < 2 Then Exit Sub

    ' Copy columns A:C, including the header row.
    Set sourceRange = wsSource.Range("A1:C" & lastRow)
    sourceRange.Copy Destination:=wsTarget.Range("A1")
End Sub

The last-row check depends on the chosen key column. If column A has blanks in its final records, the result may stop too early; use a column that is reliably filled or adopt a more suitable method for the layout. This example also starts every copy at Archive!A1, so each run replaces cells in the same area. If you want to append records instead, find the next available destination row and account for whether the source header should be copied again.

Common errors and ways to prevent them

A macro that reports a subscript error often refers to a worksheet or workbook name that does not match exactly, or to a workbook that is not open. Check spelling, spaces, punctuation, and file extensions. A protected worksheet, merged cells in a mismatched layout, or an invalid destination can also prevent a copy from completing as expected.

Unexpected source cells: Qualify every range with its worksheet rather than relying on the active sheet.
Wrong workbook: Decide whether the code should use ThisWorkbook or ActiveWorkbook, and use the intended one consistently.
Overwritten data: Confirm the destination address and whether the target area already contains information. A copy replaces cells in its destination footprint.
Missing rows: Check the column used to find the last row, especially when records contain blanks.
Unexpected formatting or formulas: Use a values-only assignment when formulas and source formatting are not wanted.

For a routine that may fail because a sheet name is supplied by a user or configuration, add error handling and a clear message rather than assuming every reference exists. Avoid suppressing all errors without checking the result: doing so can make a failed copy look successful. Also consider whether formulas in the copied range refer to other sheets or external workbooks, since those references can remain part of the copied formulas.

VBA copy versus manual copy and paste

For a one-time task, selecting cells and using the normal Excel commands may be faster than creating a macro. On Windows, Ctrl+C copies and Ctrl+V pastes. On Mac, the corresponding shortcuts are Command+C and Command+V. In Excel for Microsoft 365 on Windows, the ribbon commands are on the Home tab in the Clipboard group.

VBA becomes useful when the same operation needs to happen repeatedly, when the source and destination should be selected by rules, or when copying is one step in a larger process. A destination specified directly in VBA is also less dependent on the current selection than a macro that imitates manual keystrokes. If you need special paste behavior, such as copying column widths or pasting only formats, use Excel’s Paste Special options or the relevant VBA paste method rather than assuming a standard range copy handles every paste option.

Excel for the web can open and edit many macro-enabled workbooks, but it cannot create, run, or edit VBA macros. Use the desktop Excel application to work with VBA. Excel for Mac also supports VBA in its desktop application, although menus and some platform-specific features can differ; the range-copy examples shown here use standard Excel VBA objects and do not depend on Windows-only clipboard automation.

Frequently Asked Questions

How do I copy a range without selecting it?
Qualify the source range with its worksheet and use sourceRange.Copy Destination:=targetSheet.Range("A1"). The macro can copy directly without activating either worksheet or selecting cells.

How do I copy only values, not formulas?
Assign the source range’s Value property to an equally sized destination range, for example targetRange.Value = sourceRange.Value. This transfers calculated values and leaves the target’s existing formatting in place.

Does VBA copy formulas and formatting?
A regular Range.Copy copies formulas or values and cell formatting in the normal Excel copy manner. Relative formula references adjust for the destination. It does not automatically copy column widths.

Can I copy cells to another worksheet?
Yes. Set a worksheet variable for the source and another for the destination, then specify the target’s top-left cell. Both sheets can be in the same workbook; use workbook-qualified references for separate open workbooks.

Why does my macro say subscript out of range?
Usually, a workbook or worksheet name in the code does not exactly match an open workbook or existing sheet. Check the full workbook filename, including its extension, and the worksheet tab name.

Can I undo a VBA copy with Ctrl+Z?
No. Running VBA clears Excel’s normal undo history, and macro changes cannot be reversed with Ctrl+Z. Save a separate copy before running a macro that changes workbook contents.

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.