VBA Copy and Paste a Range of Cells in Excel

To copy a range of cells with VBA, use sourceRange.Copy Destination:=destinationRange. For example, Worksheets("Sheet1").Range("A1:C10").Copy Destination:=Worksheets("Sheet2").Range("A1") copies the range to the other sheet, including its formulas and formatting. If you need only values or a specific paste type, copy the source and use PasteSpecial instead.

Two basic ways to copy a range

Excel VBA offers two common approaches. The first uses the Copy method with its Destination argument. It is usually the simplest option when you want to duplicate cells as they are, without interacting with the clipboard. The destination argument identifies the top-left cell where the copied range should begin; Excel fills the corresponding rows and columns.

For example, this macro copies A2:C20 from a worksheet named Data to A2 on a worksheet named Archive in the same workbook:

Sub CopyRangeToAnotherSheet()
    Dim wsSource As Worksheet
    Dim wsTarget As Worksheet

    ' Save a separate copy of the workbook before running this macro.
    Set wsSource = ThisWorkbook.Worksheets("Data")
    Set wsTarget = ThisWorkbook.Worksheets("Archive")

    wsSource.Range("A2:C20").Copy Destination:=wsTarget.Range("A2")
End Sub

The second approach uses the clipboard: copy the source range, then call PasteSpecial on the destination. Choose this when the copied cells should not bring everything along. For example, you can paste values and number formats while leaving out formulas and other formatting.

Before running any macro that changes a workbook, save a separate copy. VBA changes cannot be reversed with Ctrl+Z, so keeping an untouched version is a practical safeguard.

How the destination range works

In a copy statement, the destination is normally just the top-left cell, not a range that must exactly match the source size. Copying a 10-row-by-3-column source range to a single destination cell fills a 10-row-by-3-column area starting there. If that output area contains existing data, the copied cells overwrite it, so check the destination before running the macro.

The source and destination do not need to be on the same worksheet. For worksheets in the same workbook, refer to both explicitly, as in the example above. Fully qualifying the ranges avoids relying on whichever sheet happens to be active. That makes a macro more dependable when it is run from another sheet or workbook.

You can also copy to a different location on the same sheet. For instance, changing the destination to wsSource.Range("E2") places the copied block at E2. Using a destination cell is generally clearer than selecting a range first and then issuing a separate paste command.

Choose between full copies and PasteSpecial

A regular copy is suitable when the destination should receive the cells as a complete package. It generally carries formulas, values, number formats, fonts, fills, borders, and other cell-level formatting. Relative references in formulas can adjust to their new position, just as they do when cells are copied through Excel’s interface.

Use PasteSpecial when you want to control what gets copied. The example below pastes calculated values and number formats from A2:C20 into A2:C20 on Archive. It does not paste the source formulas themselves.

Sub PasteValuesAndNumberFormats()
    Dim wsSource As Worksheet
    Dim wsTarget As Worksheet

    ' Save a separate copy of the workbook before running this macro.
    Set wsSource = ThisWorkbook.Worksheets("Data")
    Set wsTarget = ThisWorkbook.Worksheets("Archive")

    wsSource.Range("A2:C20").Copy
    wsTarget.Range("A2").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    Application.CutCopyMode = False
End Sub

The Application.CutCopyMode = False line clears Excel’s moving copy border after the paste. It is optional, but can leave the sheet looking tidier. Other useful paste constants include xlPasteValues, xlPasteFormulas, xlPasteFormats, and xlPasteAll. Pick the one that matches the outcome you need; “values” means the displayed results, not the formula that produced them.

Copy a range whose size changes

When the number of rows changes from one run to another, build the source range from the last populated row instead of hard-coding an ending row. The following example assumes column A is filled for every record and the data occupies columns A through C, with headers in row 1.

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

    ' Save a separate copy of the workbook before running this macro.
    Set wsSource = ThisWorkbook.Worksheets("Data")
    Set wsTarget = ThisWorkbook.Worksheets("Archive")

    lastRow = wsSource.Cells(wsSource.Rows.Count, "A").End(xlUp).Row
    Set sourceRange = wsSource.Range("A1:C" & lastRow)

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

This finds the last non-empty cell in column A by moving upward from the bottom of the sheet. The assumption matters: if a record can have a blank in column A while other columns contain data, column A may not identify the true last row. Choose a reliably populated column, or use a more specific way of defining the data. If column A contains only its header, the example copies that header row; adjust the logic if an empty data set should instead stop the macro.

Copy values without using the clipboard

For a values-only transfer between equally sized ranges, you can assign one range’s Value to another range’s Value. This avoids the clipboard and is often convenient for routine data movement. It does not copy formatting, comments, or formulas; formulas in the source are transferred as their current results.

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

    ' Save a separate copy of the workbook before running this macro.
    Set wsSource = ThisWorkbook.Worksheets("Data")
    Set wsTarget = ThisWorkbook.Worksheets("Archive")
    Set sourceRange = wsSource.Range("A2:C20")

    wsTarget.Range("A2").Resize(sourceRange.Rows.Count, sourceRange.Columns.Count).Value = sourceRange.Value
End Sub

The Resize call makes the destination the same height and width as the source. This is useful if you later change the source address but want the destination dimensions to follow it. When using direct assignment, matching the dimensions is important; unlike a copy operation, the assignment is not a request to carry a full set of cell features.

Copy between workbooks and worksheets

For another workbook, refer to its Workbook object and worksheet rather than assuming it is active. If the target workbook is already open, a variable can point to it; if the macro needs to open a file, use Workbooks.Open with the correct path and handle the possibility that the file is missing or already open. Always verify the workbook and sheet names, because a misspelled worksheet name causes a run-time error.

ThisWorkbook means the workbook containing the VBA code. ActiveWorkbook means the workbook that is currently active in Excel, which may be a different file. For macros stored in a personal macro workbook or add-in, that distinction can be important. Use an explicit workbook reference when you know which file should receive the data.

A macro does not need to activate a worksheet or select cells before copying. Avoiding Select and Activate usually makes code shorter and less sensitive to a user’s current selection. If the workbook is protected, the destination sheet may need to be unprotected before writing to locked cells, subject to the workbook’s protection settings.

Common problems and how to fix them

“Subscript out of range”: Check that the workbook and worksheet names match exactly, including spaces. A reference such as Worksheets("Archive") fails if the tab has a different name.
The wrong range is copied: Unqualified references such as Range("A1:C10") can point to the active sheet. Qualify the range with its worksheet, for example wsSource.Range("A1:C10").
The destination data disappears: A copy overwrites cells where the copied block lands. Check the output area or choose a destination that does not overlap information you need to keep.
Formulas change after copying: Relative cell references may adjust to the new location. Use a values-only paste if you want the results rather than formulas, or write formulas with appropriate absolute references when they must stay anchored.
PasteSpecial fails or pastes the wrong thing: Make sure a source range was copied immediately before the paste and select the intended Paste constant. For a straightforward full copy, use the Destination argument instead.
Only some records transfer: The last-row method depends on the column used to find the end. If that column has gaps, select a column that is consistently populated or define the data range another way.

Macros may be blocked by Excel’s security settings, especially in files downloaded from the internet. Only enable macros in workbooks you trust. In Microsoft 365 for Windows, VBA is edited in the Visual Basic Editor, commonly opened with Alt+F11; on many Mac keyboards, the equivalent is Option+F11, sometimes with the Fn key depending on keyboard settings. Excel for the web can open workbooks containing VBA, but it cannot create, run, or edit VBA macros; use the desktop app for these examples.

When to use VBA instead of manual copy and paste

Manual copying is often faster for a one-time task. In Excel for Microsoft 365 on Windows, select cells and press Ctrl+C, then select the destination and press Ctrl+V. On Mac, use Command+C and Command+V. For a one-off values-only paste, use the Paste Options button or the Paste Special controls available in the desktop app. Menu and shortcut behavior can vary slightly by platform and keyboard settings.

VBA is more useful when the same transfer must happen repeatedly, when the source size changes, or when a larger process needs to move data as one step. It can also make the intended source and destination explicit, reducing mistakes caused by copying the wrong selection. For simple recurring transfers, a macro may be more than you need: an Excel Table, a formula, or Power Query may be a better fit if the goal is to keep data linked, calculate results, or routinely import and reshape records.

Frequently Asked Questions

How do I copy a range to another sheet in VBA?
Qualify both worksheets and use sourceSheet.Range("A1:C10").Copy Destination:=targetSheet.Range("A1"). The destination cell is the top-left corner of the copied block.

How do I paste values only in VBA?
Copy the source range, then call targetSheet.Range("A1").PasteSpecial Paste:=xlPasteValues. For a same-size values-only transfer without the clipboard, assign the source range’s Value to a matching destination range’s Value.

Does VBA copy formulas and formatting?
A regular range copy generally copies formulas, values, and cell formatting. Formula references may adjust to the destination. Use a specific PasteSpecial type or a value assignment when you want to exclude formulas or formatting.

Do I need to select the cells before copying?
No. Refer directly to the source and destination ranges in code. Explicit references are more reliable than selecting cells and depending on the active worksheet.

Can I undo a VBA copy with Ctrl+Z?
No. Running a macro clears Excel’s normal undo history, so Ctrl+Z cannot reverse its changes. Save a separate copy of the workbook before running code that changes cell 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.