VBA Copy and Paste Range in Excel

To copy an Excel range with VBA, use sourceRange.Copy Destination:=destinationCell; Excel copies the cells to the destination, including their formulas and formatting. To transfer only the cell values, assign the source range’s Value2 to an equally sized destination range instead.

How range copying works

In VBA, a range is one cell or a rectangular group of cells on a worksheet. A copy operation has two parts: the range to copy and the first cell where the copy should begin. The source and destination can be on the same sheet or on different sheets. For example, copying A1:C10 to E1 places the copied block in E1:G10.

When you use Range.Copy with its Destination argument, VBA directs Excel to copy the source to that location without requiring a separate paste command. This is usually the simplest approach when you want a normal Excel-style copy. Formulas move as formulas, so references may adjust as they would when cells are copied and pasted in the worksheet.

Unlike assigning cell values directly, copying a range can carry cell features such as formatting and formulas. If you need a particular result—values only, formulas only, or a specific kind of formatting—choose the method deliberately rather than assuming every copy operation has the same effect.

Copy a range to another worksheet

This example copies A1:C10 from a worksheet named Source to E1 on a worksheet named Report. It uses ThisWorkbook, meaning the workbook that contains the VBA code. Change the sheet names and addresses to match your file; the destination needs only its top-left cell because Excel places the rest of the copied block relative to it.

Option Explicit

Sub CopyRangeToReport()
    Dim wb As Workbook
    Dim sourceRange As Range
    Dim destinationCell As Range

    Set wb = ThisWorkbook
    Set sourceRange = wb.Worksheets("Source").Range("A1:C10")
    Set destinationCell = wb.Worksheets("Report").Range("E1")

    ' Copy the full range to the destination's top-left cell.
    sourceRange.Copy Destination:=destinationCell
End Sub

Using explicit worksheet references is safer than relying on the active sheet. If code uses Range("A1:C10") without naming a worksheet, VBA generally uses the active sheet, which may not be the sheet you intended. A misspelled worksheet name or a sheet that does not exist causes a run-time error, so verify names before running the macro.

Copy on the same worksheet

For a same-sheet copy, identify both locations on the same worksheet. This example copies A2:B20 to D2:E20. The dimensions match because the destination begins at a single cell and the copied area is two columns wide and 19 rows tall.

Option Explicit

Sub CopyRangeOnSameSheet()
    Dim ws As Worksheet
    Dim sourceRange As Range
    Dim destinationCell As Range

    Set ws = ThisWorkbook.Worksheets("Data")
    Set sourceRange = ws.Range("A2:B20")
    Set destinationCell = ws.Range("D2")

    ' Place the copied block beside the source data.
    sourceRange.Copy Destination:=destinationCell
End Sub

If the destination overlaps the source, Excel may replace cells that are part of the original range. That can be intentional, but it can also destroy data. Before running a macro that copies onto a worksheet, check whether the source and destination overlap and whether existing destination content should be overwritten.

Copy values without formulas or formatting

When a destination should receive only the current cell results, assign values rather than using the clipboard. The destination range must have the same number of rows and columns as the source. The example below copies A1:C10 to E1:G10 on the Report sheet. It does not transfer the source formatting; any formatting already applied to E1:G10 remains in place.

Option Explicit

Sub CopyValuesOnly()
    Dim wb As Workbook
    Dim sourceRange As Range
    Dim destinationRange As Range

    Set wb = ThisWorkbook
    Set sourceRange = wb.Worksheets("Source").Range("A1:C10")
    Set destinationRange = wb.Worksheets("Report").Range("E1").Resize( _
        sourceRange.Rows.Count, sourceRange.Columns.Count)

    ' Transfer results, not formulas or source formatting.
    destinationRange.Value2 = sourceRange.Value2
End Sub

For instance, if a source cell contains a formula that currently displays 25, assigning its Value2 transfers the result 25 rather than the formula. This can be useful for a report or a static extract. It also means the destination will not update when the source formula later recalculates. Value2 transfers values without Excel’s Currency or Date data subtypes; dates and times are represented by their underlying serial values, so apply an appropriate number format if the destination should display them as dates or times.

Use PasteSpecial for selected content

Use PasteSpecial when you want Excel to perform a copy-and-paste operation but need to choose what is pasted. For example, xlPasteValues pastes values, while xlPasteFormats pastes formatting. The following example pastes values and number formats from A1:C10 to E1 on Report, while leaving other formatting choices to Excel’s selected paste type.

Option Explicit

Sub CopyValuesAndNumberFormats()
    Dim wb As Workbook
    Dim sourceRange As Range
    Dim destinationCell As Range

    Set wb = ThisWorkbook
    Set sourceRange = wb.Worksheets("Source").Range("A1:C10")
    Set destinationCell = wb.Worksheets("Report").Range("E1")

    ' Copy, then paste values together with their number formats.
    sourceRange.Copy
    destinationCell.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    Application.CutCopyMode = False
End Sub

Clearing Application.CutCopyMode removes Excel’s moving copy border and ends copy mode. It is not required for every macro, but it can leave the workbook in a tidier state after a clipboard-based operation. If you need only values and do not need number formats, use xlPasteValues instead. When you need formulas, formats, or other paste options, choose the matching Excel paste type rather than expecting a values-only paste to retain them.

Copy a range whose size changes

For a list that grows over time, set the source range based on its last occupied row instead of hard-coding the row number. This example assumes row 1 contains headers, data begins in column A, and column A is populated for every record. It copies columns A:C through the last nonblank cell in column A to A1 on the Report sheet.

Option Explicit

Sub CopyChangingList()
    Dim wb As Workbook
    Dim sourceSheet As Worksheet
    Dim destinationSheet As Worksheet
    Dim lastRow As Long
    Dim sourceRange As Range
    Dim destinationCell As Range

    Set wb = ThisWorkbook
    Set sourceSheet = wb.Worksheets("Source")
    Set destinationSheet = wb.Worksheets("Report")

    ' Find the last nonblank cell in the key column.
    lastRow = sourceSheet.Cells(sourceSheet.Rows.Count, "A").End(xlUp).Row
    Set sourceRange = sourceSheet.Range("A1:C" & lastRow)
    Set destinationCell = destinationSheet.Range("A1")

    ' Copy the header and all rows found in column A.
    sourceRange.Copy Destination:=destinationCell
End Sub

The last-row method depends on the chosen column. If column A has gaps or can be blank for a valid record, its last nonblank cell may not represent the bottom of the full dataset. Pick a column that is consistently filled, or use a different way to define the data boundary. Also consider clearing old destination rows when the new list is shorter than the previous one; copying a smaller range does not automatically remove leftover cells below it.

Choose the right copy method

The best approach depends on what the destination needs to retain. Use these rules of thumb:

Use Copy Destination:= for a straightforward Excel-style copy that should carry formulas and cell formatting.
Assign Value2 when you want the current results without formulas or source formatting. Make the destination the same size as the source.
Use PasteSpecial when you need a specific paste type, such as values only or formats only.
Use Formula or FormulaR1C1 assignments when you specifically need to write formulas through VBA and want to control how references are expressed. These do not copy cell formatting.

One common misconception is that “copy” always means “copy formulas.” A copied formula may adjust relative references based on its new location, whereas a values-only transfer freezes the calculated result. For example, a formula referring to the cell immediately to its left may still refer to the cell immediately to its left after a normal copy. If the destination must keep the exact original formula text or calculation behavior, inspect the references and choose an appropriate formula-writing method rather than assuming the copy will preserve every reference unchanged.

Common problems and safe execution

A copy macro can fail or produce unexpected results for several practical reasons. Check these points when troubleshooting:

Wrong active sheet: qualify ranges with a worksheet, as in Worksheets("Data").Range("A1:B5"), instead of relying on whichever sheet is selected.
Destination size: when assigning values directly, size the destination to match the source. A single-cell destination is enough for Copy Destination:=, but not for a direct multi-cell array assignment.
Protected sheets: worksheet protection can prevent changes to locked destination cells. Confirm the sheet’s protection settings and the permissions available to the macro.
Merged cells: merged areas can make range dimensions and paste destinations behave differently from ordinary rectangular cells. Avoid merging cells in data blocks that need routine automated copying.
Old clipboard state: if using PasteSpecial, make sure the source was copied first and clear copy mode afterward when appropriate.
Stale output: copying fewer rows than a previous run leaves old cells below the new output unless the macro deliberately clears them.

VBA macros run in the desktop Excel application; Excel for the web does not run VBA macros. On Windows, open the Visual Basic Editor with Alt+F11. On Mac, Option+F11 is the usual equivalent, though some keyboards require Fn as well; the Developer tab’s Visual Basic command is another route when available. Excel for Mac supports much of the VBA object model, but Windows-specific automation and some integrations may not work identically.

Save the workbook as an Excel Macro-Enabled Workbook (.xlsm) if it needs to retain its VBA project, and save a separate copy before running a macro that changes data. Macro changes cannot be reversed with Ctrl+Z, so use a backup or test on sample data first. The corresponding undo shortcut is Command+Z on Mac, but it does not restore changes made by a VBA macro either.

Frequently Asked Questions

How do I copy and paste a range in VBA?

Set a source range and destination cell, then use sourceRange.Copy Destination:=destinationCell. For example, copying A1:C10 to E1 fills E1:G10. Name the worksheets explicitly to avoid copying from or to an unintended active sheet.

How do I paste values only with VBA?

For a direct transfer, set a destination range to the same dimensions as the source and assign destinationRange.Value2 = sourceRange.Value2. Alternatively, copy the source and use PasteSpecial Paste:=xlPasteValues. The direct assignment avoids the clipboard and does not copy source formatting.

Can VBA copy a range without using the clipboard?

Yes. Assigning Value2 transfers values without a copy-and-paste operation. Formula properties can also be assigned when formulas are required. These assignments do not reproduce all the cell features of a normal copy, so use Copy Destination:= or PasteSpecial when you need Excel’s copy behavior or selected paste options.

Why does my copied formula change?

Normal copying adjusts relative references according to the destination, just as worksheet copy and paste does. Absolute references marked with dollar signs stay fixed in the specified row or column. If you want only the displayed result, paste or assign values; if you need to keep a reference fixed, review whether the formula should use absolute references.

Can I run a VBA copy macro in Excel for the web?

No. Excel for the web can open and edit many workbooks, but it does not run VBA macros. Use desktop Excel for Windows or Mac to run VBA, and check any platform-specific dependencies if the workbook was built for only one operating system.

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.