Copy and Paste a Range in Excel VBA: Methods and Examples

In Excel VBA, copy a range with the Range.Copy method and its Destination argument; for example, identify a source range and a destination cell, then copy directly without selecting either range. Use PasteSpecial when you need only values, formulas, or formatting, rather than everything in the source cells.

Copy a range directly to a destination

The simplest approach is to refer to both ranges explicitly. The source range contains the cells to copy; the destination reference identifies the top-left cell where the copied block should begin. Excel fills the corresponding rows and columns at the destination, so you usually do not need to specify the destination block’s full size.

Sub CopyRangeToAnotherSheet()
    Dim sourceRange As Range
    Dim destinationCell As Range

    ' Identify the source and destination explicitly.
    Set sourceRange = ThisWorkbook.Worksheets("Data").Range("A1:C10")
    Set destinationCell = ThisWorkbook.Worksheets("Archive").Range("A1")

    sourceRange.Copy Destination:=destinationCell
End Sub

This example copies A1:C10 from the Data worksheet to a block beginning at A1 on the Archive worksheet. It copies cell contents and the usual cell features, including formulas, number formats, and formatting. The source and destination worksheets must exist in the workbook containing the macro. ThisWorkbook means the workbook where the VBA code is stored, which is safer than relying on whichever workbook happens to be active.

The direct Destination form avoids the extra clipboard step used by the manual Copy and Paste commands. It is usually the clearest choice when the goal is to copy a whole range exactly. It also avoids Select and Activate, which can make a macro dependent on the user’s current selection or active sheet.

Use a complete, reusable macro

A procedure needs to run in a suitable VBA location, such as a standard module in the workbook. In desktop Excel for Microsoft 365 on Windows, press Alt+F11 to open the Visual Basic Editor, choose Insert > Module, and paste the procedure there. On Mac, the VBA editor is available in desktop Excel, but the keyboard shortcut differs by keyboard and Excel setup; use the Developer tab or Excel’s Tools > Macro menu if the shortcut is unavailable. Excel for the web does not run VBA macros.

Before running a macro that changes workbook contents, save a copy of the workbook. Macro actions cannot be reversed with Ctrl+Z, and that limitation also applies when the macro performs a straightforward copy. The following procedure checks that the named sheets can be referred to in the workbook and illustrates a common source-to-destination task.

Sub CopyReportBlock()
    Dim sourceRange As Range
    Dim destinationCell As Range

    ' Copy a fixed report block into a second worksheet.
    Set sourceRange = ThisWorkbook.Worksheets("Report").Range("B2:F20")
    Set destinationCell = ThisWorkbook.Worksheets("Summary").Range("A4")

    sourceRange.Copy Destination:=destinationCell
End Sub

Change the worksheet names and addresses to match the workbook. Worksheet names in VBA must match the visible tab names, including spaces and punctuation. If a sheet name is misspelled or does not exist, VBA raises an error rather than silently choosing another sheet.

Choose a fixed or changing source range

A fixed address such as A1:C10 is appropriate when the report always has the same layout. If the number of records changes, determine the final row at run time instead. The next example assumes column A contains a heading in A1 and a nonblank identifier for every data row. It copies columns A through C, including the header, to the Archive sheet.

Sub CopyChangingList()
    Dim wsSource As Worksheet
    Dim wsDestination As Worksheet
    Dim sourceRange As Range
    Dim lastRow As Long

    Set wsSource = ThisWorkbook.Worksheets("Data")
    Set wsDestination = ThisWorkbook.Worksheets("Archive")

    ' Find the last used row in column A.
    lastRow = wsSource.Cells(wsSource.Rows.Count, "A").End(xlUp).Row
    Set sourceRange = wsSource.Range("A1:C" & lastRow)

    sourceRange.Copy Destination:=wsDestination.Range("A1")
End Sub

The last-row method starts at the bottom of column A and moves upward to the next nonempty cell, much like pressing Ctrl+Up from the bottom of a worksheet. On Mac, the corresponding navigation key is Command+Up. The code uses the VBA End and xlUp members, so the user’s keyboard shortcut does not affect what the macro does.

This method depends on the assumption that column A has no gaps that make the final record hard to identify. If the key column can be blank on the last records, find the last row using a column that is reliably populated, or use a different method based on the workbook’s structure. Also consider what should happen when the sheet contains only a heading: the sample will copy that heading, which may or may not be the intended result.

Copy only values, formulas, or formatting

Range.Copy copies more than displayed values. When only part of the cell information is wanted, copy first and then use PasteSpecial with the appropriate paste type. In this example, the destination receives the source’s calculated or entered values but not its formulas or formatting.

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

    Set sourceRange = ThisWorkbook.Worksheets("Data").Range("A1:C10")
    Set destinationCell = ThisWorkbook.Worksheets("Archive").Range("A1")

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

xlPasteValues is useful for creating a snapshot of results, removing formulas from a report, or transferring results to a destination where formulas would refer to the wrong cells. The cells retain their existing destination formatting. If you instead want formulas, use xlPasteFormulas; for formatting alone, use xlPasteFormats. These are Excel’s built-in paste-type constants.

For a values-only transfer, you can also assign one range’s values to another without using the clipboard. The destination block must have the same number of rows and columns as the source. This approach does not copy formulas, formatting, column widths, or other cell features.

Sub AssignValuesOnly()
    Dim sourceRange As Range
    Dim destinationRange As Range

    Set sourceRange = ThisWorkbook.Worksheets("Data").Range("A1:C10")
    Set destinationRange = ThisWorkbook.Worksheets("Archive").Range("A1:C10")

    ' Transfer values without copying formats or formulas.
    destinationRange.Value2 = sourceRange.Value2
End Sub

Value2 transfers the underlying values returned by Excel, including formula results, rather than the formulas themselves. Dates are represented as their underlying numeric serial values through Value2; if the destination needs to display dates as dates, apply an appropriate number format there. Use Copy and PasteSpecial instead when you need Excel’s paste behavior or a particular combination of cell features.

Understand destination size and formula behavior

For a normal copy, the destination cell is the top-left corner of the pasted block. A source with 10 rows and 3 columns therefore occupies 10 rows and 3 columns at the destination. Make sure those destination cells can be changed: a protected sheet, merged cells, or existing data that must be preserved can cause problems or unwanted overwriting.

When a copied formula contains relative references, Excel generally adjusts those references according to the move from source to destination, as it does during a worksheet copy-and-paste operation. Absolute references marked with dollar signs remain fixed. For instance, copying a formula that refers to A1 down one row can change its relative reference to A2, while a reference to $A$1 remains $A$1. If the destination should retain the source formula text exactly rather than adjust references, consider whether the formula should use absolute references or whether a different transfer method is required.

Copying a block does not automatically make a destination worksheet’s column widths match the source. If widths matter, copy them as a separate operation or set the destination widths explicitly. Similarly, copying a cell range does not copy an entire worksheet, its sheet-level settings, or every workbook object. For a complete sheet, Excel’s worksheet-copy features are more appropriate than copying a range.

Copy between workbooks and avoid active-sheet mistakes

Ranges can be copied between worksheets in the same workbook or between open workbooks. For another workbook, qualify both the source and destination with explicit Workbook and Worksheet references. This prevents a macro from accidentally using the active workbook, which can change when a user clicks another file.

For example, a macro can refer to a workbook opened earlier by assigning it to a Workbook variable, then use that variable to identify its worksheet. The destination workbook must be open and writable, and the destination sheet must exist. If you use ThisWorkbook for the source, remember that it means the workbook containing the macro, not necessarily the workbook currently being viewed.

Using unqualified references such as Range(“A1:C10”) or Worksheets(“Archive”) is risky in a multi-sheet or multi-workbook procedure. VBA may interpret them against the active sheet or active workbook. Fully qualifying the reference makes the intended source and destination clear and helps prevent copying to the wrong file.

Common errors and practical safeguards

Many copy-macro errors come from assumptions about workbook state rather than from the Copy method itself. Before running a macro, check the sheet names, source address, destination address, and whether the destination is protected. If a procedure relies on a changing data range, verify that its chosen key column is populated through the final record.

Subscript out of range: A referenced worksheet or workbook name may be wrong, or the workbook may not be open.
PasteSpecial failed: The source may not have been copied, the destination may be protected, or the operation may not be valid for the selected cells. Keep the Copy and PasteSpecial steps together.
Unexpected results: The macro may be using ActiveSheet or ActiveWorkbook instead of the intended sheet or file. Qualify every range with its worksheet.
Data was overwritten: The destination block may already contain information. Confirm the target area before running the macro.
Copying appears slow: Avoid repeatedly copying small ranges inside a loop when one larger range operation or direct value assignment will do.

For a values-only operation, direct assignment can be efficient because it avoids using Excel’s copy mode. For a full copy, the Copy method remains useful because it preserves more of the source cell’s behavior and appearance. Set Application.CutCopyMode to False after a PasteSpecial operation if you want to clear Excel’s marching-ants copy outline; doing so is not necessary after every direct Destination copy.

Macros, workbook formats, and safe use

To keep VBA code in the workbook, save it in a macro-enabled Excel workbook, usually with the .xlsm extension. A standard .xlsx workbook does not store VBA project code; saving a macro-enabled file as .xlsx can remove that code. Only enable macros in files from sources you trust, since VBA can do more than copy cells and may alter files or interact with other parts of the system.

VBA macros run in desktop Excel, not Excel for the web. Excel for the web can work with many workbook contents, but it does not execute VBA procedures. If a workbook must be automated in a browser, Office Scripts may be an alternative where the account and environment support them, but Office Scripts use a different language and API and are not drop-in VBA replacements.

Frequently Asked Questions

How do I copy a range to another worksheet in VBA?
Set a Range variable for the source and identify a destination cell on the other worksheet, then use the Copy method with its Destination argument. Qualify both ranges with their worksheet names so the macro does not depend on the active sheet.

How do I paste values only in Excel VBA?
Copy the source range, then call PasteSpecial on the destination with xlPasteValues. For a straightforward values-only transfer between equal-sized ranges, assigning the destination’s Value2 property from the source’s Value2 property avoids the clipboard.

Do I need to select or activate cells before copying?
No. A macro can refer directly to worksheet ranges and copy them without selecting or activating a sheet. Direct references are generally more dependable, especially when the workbook has multiple sheets or users may have different cells selected.

Can VBA copy a range to another workbook?
Yes, provided the workbooks are open and the source and destination are explicitly identified. Reference each workbook and worksheet rather than relying on ActiveWorkbook, and confirm the destination workbook is writable before copying.

Why can’t I undo a VBA copy with Ctrl+Z?
Running VBA code generally clears Excel’s ordinary undo history. Save a separate copy before running a macro that changes important data, and test the procedure on a sample workbook or duplicate sheet first.

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.