VBA Code to Copy a Range in Excel

In Excel VBA, copy a range by calling Range.Copy and naming the destination range. For example, sourceSheet.Range("A1:C10").Copy Destination:=targetSheet.Range("A1") copies cells A1:C10 to a worksheet named Report, starting at A1.

Basic VBA code to copy a range

This complete macro copies a fixed range from one worksheet to another in the workbook containing the code. Change the sheet names and range addresses to match your workbook.

Option Explicit

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

    ' Set the source and destination worksheets.
    Set sourceSheet = ThisWorkbook.Worksheets("Data")
    Set targetSheet = ThisWorkbook.Worksheets("Report")

    ' Copy the range, beginning at cell A1 on the destination sheet.
    sourceSheet.Range("A1:C10").Copy Destination:=targetSheet.Range("A1")
End Sub

The destination argument is the upper-left cell where the copied block should begin. Excel fills the corresponding rows and columns from that point, so a three-column, ten-row source range occupies three columns and ten rows at the destination. The destination does not need to be the same address as the source.

ThisWorkbook means the workbook that contains the macro. Qualifying each range with a worksheet variable is important: it makes clear which sheet each address refers to and avoids accidentally copying from whichever sheet happens to be active. If a sheet name is misspelled or does not exist in that workbook, VBA raises an error.

What Range.Copy copies

A normal range copy behaves much like copying cells in Excel’s interface. It transfers cell contents and their associated cell formatting, rather than just displaying the same text. Depending on the cells, that can include formulas, number formats, borders, fills, and other cell-level settings. It is usually the right choice when you want the destination to resemble the source.

Formulas remain formulas. Excel adjusts relative cell references as it ordinarily does when a formula is copied to a new location. For instance, copying a formula that refers to the cell immediately to its left can make the copied formula refer to the cell immediately to the left of its new location. Absolute references marked with dollar signs remain fixed in the usual way. If you need the formula’s current result rather than a working formula, use a values-only method instead.

Copying cells does not necessarily reproduce every feature of a worksheet layout. For example, column widths are not reliably carried over as part of an ordinary cell-range copy. If matching widths matter, copy or set them separately. A copied range also does not insert cells or move existing destination content out of the way; the copy writes into the destination area. Check what already occupies that area before running the macro.

Choose the right copy method

The method depends on what you want the destination cells to contain. These are common choices:

Full cell copy: Use Range.Copy Destination:=... when formulas, formatting, and cell contents should travel together.
Values only: Assign the source range’s Value to the destination range’s Value when you want results without formulas or source formatting.
One feature only: Use PasteSpecial when you need to transfer only formats, formulas, or another supported paste type.
Same-sheet copy: Use the same syntax with source and destination ranges on one explicitly named worksheet.

For a values-only transfer, the source and destination ranges must have matching dimensions. This example copies a ten-row by three-column block from Data to Report:

Option Explicit

Sub CopyValuesOnly()
    Dim sourceSheet As Worksheet
    Dim targetSheet As Worksheet
    Dim sourceRange As Range
    Dim targetRange As Range

    ' Use equal-sized ranges for a direct value assignment.
    Set sourceSheet = ThisWorkbook.Worksheets("Data")
    Set targetSheet = ThisWorkbook.Worksheets("Report")
    Set sourceRange = sourceSheet.Range("A1:C10")
    Set targetRange = targetSheet.Range("A1:C10")

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

This assignment copies the values currently held in the cells. If a source cell contains a formula, its calculated result is placed in the destination as a value, so the destination will not recalculate from that formula. The destination’s existing formatting remains in place. This is often useful for creating a fixed-value report, but it is not a substitute for a full copy when formulas need to stay live.

Copy formats or another paste type

When the destination should keep its own values but adopt the source formatting, copy first and then use PasteSpecial with the formats option. This example uses the Excel constant xlPasteFormats:

Option Explicit

Sub CopyFormatsOnly()
    Dim sourceSheet As Worksheet
    Dim targetSheet As Worksheet
    Dim sourceRange As Range
    Dim targetRange As Range

    ' Set ranges with matching dimensions.
    Set sourceSheet = ThisWorkbook.Worksheets("Data")
    Set targetSheet = ThisWorkbook.Worksheets("Report")
    Set sourceRange = sourceSheet.Range("A1:C10")
    Set targetRange = targetSheet.Range("A1:C10")

    ' Copy formatting without replacing destination values.
    sourceRange.Copy
    targetRange.PasteSpecial Paste:=xlPasteFormats
    Application.CutCopyMode = False
End Sub

Unlike the direct Copy Destination form, this two-step approach uses Excel’s copy-and-paste operation. Setting Application.CutCopyMode to False clears the moving copy border when the operation is done. Other PasteSpecial options are available, but choose the option that matches the content you intend to retain; a paste choice that includes formulas or values can replace existing destination content.

Copy a range with a variable last row

A fixed address is straightforward but may not fit a list that grows over time. To copy all populated rows in columns A through C, one common approach is to find the last nonblank cell in column A and use that row number to build the range. This assumes column A is populated for every record you want included.

Option Explicit

Sub CopyCurrentData()
    Dim sourceSheet As Worksheet
    Dim targetSheet As Worksheet
    Dim lastRow As Long

    ' Identify the sheets containing the list and its destination.
    Set sourceSheet = ThisWorkbook.Worksheets("Data")
    Set targetSheet = ThisWorkbook.Worksheets("Report")

    ' Find the last nonblank cell in column A.
    lastRow = sourceSheet.Cells(sourceSheet.Rows.Count, "A").End(xlUp).Row

    ' Copy columns A:C from the first row through the last data row.
    sourceSheet.Range("A1:C" & lastRow).Copy _
        Destination:=targetSheet.Range("A1")
End Sub

The expression starts at the bottom of column A and moves upward to the first nonblank cell it encounters. If column A is completely empty, this method returns row 1, so the example would still attempt to copy A1:C1. If that situation is possible, add a check for an empty source before copying. Also choose a column that is reliably filled; using a column with gaps can make the detected last row shorter than the full data set.

Steps to add and run the macro

Save a backup copy of the workbook before running a macro. VBA changes cannot be undone with Ctrl+Z.
Save the working workbook as an Excel Macro-Enabled Workbook (.xlsm) if it contains VBA that must be retained.
Open the Visual Basic Editor. On Windows, Alt+F11 is the usual shortcut. On Mac, use the Developer tab’s Visual Basic command; keyboard shortcuts can vary with the keyboard and macOS settings.
In the editor, choose Insert > Module, then paste the macro into the module.
Replace the example worksheet names and range addresses with the ones in your workbook. Confirm that the destination area is safe to overwrite.
Return to Excel, choose Developer > Macros, select the macro, and choose Run. If the Developer tab is not visible, enable it in Excel’s ribbon settings.

Only enable macros in files you trust. Excel’s macro security settings can prevent a macro from running, particularly for files from the internet or other untrusted locations. Do not change security settings casually just to run an unfamiliar workbook.

Common errors and how to avoid them

Subscript out of range: A worksheet name in the code does not match a sheet in ThisWorkbook. Check spelling, spaces, and punctuation in the sheet tab name.
Wrong sheet gets copied: A range is unqualified, so it refers to the active sheet or another unintended context. Qualify it with a worksheet variable, as in the examples.
Destination data disappears: Copying into occupied cells replaces their contents. Pick an empty destination or deliberately clear only the area you intend to replace.
Only part of the list transfers: The column used to find the last row has blanks near the bottom. Use a consistently populated key column or a different method that matches the data layout.
Values appear instead of formulas: The macro uses a value assignment or a values-only paste. Use a full range copy if the formulas themselves must be transferred.
Formatting or widths look different: A normal range copy does not guarantee that every worksheet layout feature, such as column widths, matches. Copy those separately if required.

When debugging, step through the macro in the Visual Basic Editor and inspect the sheet names and range addresses. A useful precaution is to test on a duplicate workbook with a small sample before applying a macro to a large or important data set.

Copying within the same sheet or another workbook

For a same-sheet copy, the source and destination can both be qualified by one worksheet. For example, copying A1:C10 to E1 on the Data sheet means using sourceSheet.Range("A1:C10").Copy Destination:=sourceSheet.Range("E1"). The ranges should not be confused with a move: the source remains in place after a copy.

To copy between two open workbooks, set worksheet variables from the appropriate workbook objects rather than assuming both sheets are in ThisWorkbook. For example, a source workbook can be referred to through a workbook variable set to an open workbook, while the destination is referred to through another workbook variable. Confirm that the intended workbooks are open and that the destination sheet exists. Workbook names and paths must be handled accurately if the macro opens files itself.

If the goal is to create a new worksheet containing a copy of an entire sheet, copying a range is not the same operation as copying a worksheet. A range copy transfers cells into an existing sheet; a sheet-copy operation duplicates the worksheet object and its broader sheet-level structure. Choose based on whether you need only a block of cells or an entire sheet.

Frequently Asked Questions

What is the simplest VBA code to copy a range?
Use Worksheets("Data").Range("A1:C10").Copy Destination:=Worksheets("Report").Range("A1"). For dependable code, qualify the worksheets through a workbook object so the macro uses the intended file.

Does VBA Range.Copy copy formulas and formatting?
Yes. A normal range copy transfers formulas and cell formatting along with cell contents, much like copying cells in Excel. Relative formula references adjust to the destination as they do during a regular worksheet copy.

How do I copy only values in VBA?
Assign matching ranges with targetRange.Value = sourceRange.Value. This transfers current results rather than formulas and does not copy the source formatting.

How can I copy a range to the next empty row?
First determine the destination row by searching upward in a column that is consistently populated, then use that row as the destination’s starting cell. The right column and search method depend on how your sheet is structured; verify the calculated row before writing over existing data.

Can I undo a VBA range copy with Ctrl+Z?
No. Running a VBA macro generally clears Excel’s normal undo history, so Ctrl+Z cannot reverse its changes. Save a separate copy of the workbook before running a macro that changes cells.

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.