Excel VBA Copy Range to Another Sheet: Methods and Tips

To copy a range to another worksheet with Excel VBA, qualify the source and destination sheets and use Range.Copy Destination:=. For example, Worksheets("Data").Range("A1:D20").Copy Destination:=Worksheets("Report").Range("A1") copies the cells, including their contents and formatting, to the matching-sized area starting at A1 on Report.

The basic VBA pattern

Put this macro in a standard VBA module, replacing the sheet names and range with the ones in your workbook. This example assumes the macro is stored in the workbook that contains both worksheets.

Sub CopyRangeToAnotherSheet()
    Dim wb As Workbook
    Dim sourceSheet As Worksheet
    Dim destinationSheet As Worksheet

    ' Use the workbook that contains this macro.
    Set wb = ThisWorkbook
    Set sourceSheet = wb.Worksheets("Data")
    Set destinationSheet = wb.Worksheets("Report")

    ' Copy the range, including its normal cell content and formatting.
    sourceSheet.Range("A1:D20").Copy _
        Destination:=destinationSheet.Range("A1")
End Sub

The destination argument is the upper-left cell where the copied block should begin. Because the source is four columns by twenty rows, Excel places the copied block in A1:D20 on the destination sheet. You do not need to select either sheet or activate either range. In fact, avoiding Select and Activate makes the procedure less dependent on which sheet happens to be active.

In Excel for Microsoft 365 on Windows, open the VBA editor with Alt+F11, choose Insert > Module, and paste the macro into the module. If the Developer tab is not visible, enable it through File > Options > Customize Ribbon and select Developer. On Mac, use Developer > Visual Basic if the tab is enabled; the keyboard shortcut can vary with the keyboard and function-key settings. Excel for the web can open a workbook containing VBA, but it cannot create, edit, or run VBA macros.

What Range.Copy copies

Range.Copy Destination:= is the convenient choice when you want a normal worksheet copy. It copies cell contents and formatting, including formulas, number formats, borders, fill, and font formatting. Relative references in copied formulas are adjusted as they would be when copying cells in the grid. For instance, a formula referring to the cell immediately to the left will generally continue to refer to the cell immediately to the left at the destination.

The command copies cells, not every property associated with an entire worksheet. Row heights and column widths are not reliably replicated just because you copied a rectangular range. If the destination must visually match the source, copy column widths separately or set the destination dimensions explicitly. Likewise, copying cells does not transfer a worksheet’s tab color, print settings, or other sheet-level features.

Copying into a destination that already contains data replaces the cells in the target area, but it does not clear unrelated cells outside that area. A source of A1:D20 copied to B2 therefore affects B2:E21; existing values elsewhere remain. Check the destination coordinates before running a macro, especially if the target sheet contains formulas or manually maintained notes.

Copy values without formulas or formatting

If the goal is to transfer results rather than formulas, direct value assignment is usually simpler than using the clipboard. It assigns the source cells’ current values to the destination cells, without copying number formats, fill, borders, or formulas themselves. The source and destination ranges must have the same dimensions.

Sub CopyValuesOnly()
    Dim wb As Workbook
    Dim sourceSheet As Worksheet
    Dim destinationSheet As Worksheet

    ' ThisWorkbook is the workbook containing this macro.
    Set wb = ThisWorkbook
    Set sourceSheet = wb.Worksheets("Data")
    Set destinationSheet = wb.Worksheets("Report")

    ' The two ranges are both four columns by twenty rows.
    destinationSheet.Range("A1:D20").Value = _
        sourceSheet.Range("A1:D20").Value
End Sub

For example, if Data!A1 contains a formula that currently returns 25, assigning values places 25 in Report!A1 rather than the formula. The displayed result is preserved, but it will not recalculate when the source inputs change. If you need formulas and formatting, use the Copy method instead. If you want values plus a chosen format, copy first and use PasteSpecial with an appropriate paste type; remember that this approach uses Excel’s copy/paste state and should clear it afterward with Application.CutCopyMode = False.

Choose the right destination range

For a fixed-size block, specify the top-left destination cell, as in the basic example. Excel infers the size from the source range. You can also specify a destination block of the same dimensions, but there is little benefit for a straightforward copy. If your destination should start lower down, change only that top-left reference: copying A1:D20 to F5 fills F5:I24.

When you want to append records beneath existing data, identify the next available row rather than hard-coding a destination. This example assumes row 1 contains headers, records start in row 2, and every record has a value in column A. It copies columns A:D through the last used record to the next available row in Report.

Sub AppendRecords()
    Dim wb As Workbook
    Dim sourceSheet As Worksheet
    Dim destinationSheet As Worksheet
    Dim lastSourceRow As Long
    Dim nextDestinationRow As Long

    ' The example uses this workbook and column A to find records.
    Set wb = ThisWorkbook
    Set sourceSheet = wb.Worksheets("Data")
    Set destinationSheet = wb.Worksheets("Report")

    lastSourceRow = sourceSheet.Cells(sourceSheet.Rows.Count, "A").End(xlUp).Row
    If lastSourceRow < 2 Then Exit Sub

    nextDestinationRow = destinationSheet.Cells( _
        destinationSheet.Rows.Count, "A").End(xlUp).Row + 1

    ' Copy records only; the header row is not included.
    sourceSheet.Range("A2:D" & lastSourceRow).Copy _
        Destination:=destinationSheet.Cells(nextDestinationRow, "A")
End Sub

The last-row method works because it starts at the bottom of column A and moves upward to the first non-empty cell. It is only dependable if column A is populated for every record you need. If records can have blanks in that column, choose a consistently populated column or use a different way of identifying the table boundary. Also consider what should happen when Report is completely empty: this example calculates row 1 as the next destination row, which is fine if the destination has no header, but not if you need to preserve a header in row 1. In that case, set the first append row to 2 when the destination has no records.

Copy between workbooks

For two workbooks, qualify both worksheets through their workbook objects. A common source of mistakes is relying on ActiveWorkbook or an unqualified Worksheets("Data"). The active workbook can change while a macro runs, and an unqualified worksheet reference is resolved against the active workbook. Instead, set explicit workbook variables and refer to each sheet through the correct one.

ThisWorkbook means the workbook containing the VBA code. ActiveWorkbook means the workbook currently active in Excel, which may be different—for example, if the macro is stored in the Personal Macro Workbook. If the macro is stored separately from the source and destination files, identify the workbooks deliberately, such as by setting variables to already-open workbook objects. If the destination file is not open, the macro must open it before referring to its worksheet and should account for saving and closing it afterward.

Workbook and worksheet names must match the actual names, including spaces. A worksheet tab named Sales Data is referenced as Worksheets("Sales Data"). A missing or misspelled sheet name raises a run-time error, so verify names before troubleshooting the copy statement itself. Fully qualifying the objects also prevents a macro from accidentally copying to a sheet with the same name in the wrong workbook.

Common errors and how to fix them

Subscript out of range: Excel cannot find the named workbook or worksheet in the object you referenced. Check the tab name, workbook variable, and whether the workbook is open.
Nothing appears to happen: Confirm that the macro ran, the source range contains data, and the destination sheet is the one you intended. A copy can succeed while its result is off-screen or on another workbook.
The copy overwrites important cells: The destination’s top-left address determines where the full block lands. Calculate the resulting bottom-right cell before running the macro.
Only part of the data is copied: A dynamic last-row calculation may be based on a column with blanks. Use a column populated on every record or adjust the boundary logic.
Formatting differs: Check whether you used value assignment, which transfers values only, or the Copy method, which transfers cell formatting. Row height and column width need separate handling.
Macro will not run: Save the workbook in a macro-enabled format such as .xlsm, and review Excel’s macro security settings. Do not enable macros in files from untrusted sources.

Another frequent misconception is that a macro must select the source range before copying it. It does not. Code such as sourceSheet.Range("A1:D20").Copy Destination:=destinationSheet.Range("A1") directly identifies both locations. Selecting ranges adds extra steps and can fail when a sheet is hidden, another workbook becomes active, or the active sheet is not what the code assumes.

Copying versus moving, formulas, and tables

Copying leaves the original cells in place; it does not remove them. To move data, copying and then clearing or deleting the source are separate actions, and they have different consequences. Clearing contents preserves cell formatting, while deleting cells can shift other data. Decide which behavior you need before adding a cleanup step to a macro.

If the range is an Excel Table, copying its cells may transfer the table’s displayed contents and formats, but it does not necessarily create a second independent table with the same table structure and name. For reusable data workflows, consider whether you need a plain range, a new table, or a copy of the entire worksheet. A worksheet copy is a different operation from copying a range and can also bring along sheet-level settings.

When formulas are copied, relative references adjust according to the source-to-destination offset, while absolute references marked with dollar signs remain fixed in the relevant row or column. This is normal Excel copy behavior, not a VBA-specific recalculation rule. If formulas should continue referring to the original locations exactly, inspect whether their references need dollar signs, or transfer their calculated values instead.

Run the macro safely

Before running a macro that writes to a worksheet, save a separate copy of the workbook. VBA actions generally cannot be undone with Ctrl+Z, so an accidental overwrite may not be recoverable through Excel’s Undo command. A backup is particularly important when the destination contains manually entered data or when the macro appends many rows.

Test with a small, disposable range first and confirm the copied cells, formulas, and formatting. Check that the macro points to the intended workbook and that its destination does not overlap important data. If the procedure is run repeatedly, decide whether repeated copies should replace a fixed output area, append new records, or clear an old output first; those are different workflows and should be coded explicitly rather than left to chance.

VBA macros are supported in desktop Excel for Windows and Mac, though some platform-specific features and shortcuts differ. Excel for the web does not execute VBA; an alternative for browser-based automation is Office Scripts, where available, but it uses a different scripting model and is not VBA. For a simple manual copy, ordinary worksheet copy and paste may be enough; VBA is most useful when the same operation must be repeated, parameterized, or incorporated into a larger process.

Frequently Asked Questions

How do I copy a range to another sheet without selecting it?
Use a fully qualified source and destination, such as Worksheets("Data").Range("A1:D20").Copy Destination:=Worksheets("Report").Range("A1"). Qualify both worksheets through the intended workbook when more than one workbook is open.

How do I copy only values with VBA?
Assign one range’s Value property to another range of the same dimensions: destinationSheet.Range("A1:D20").Value = sourceSheet.Range("A1:D20").Value. This transfers calculated results or constants, not formulas or cell formatting.

Can VBA copy a range to the next empty row?
Yes. Find the last occupied row in a reliably populated column with Cells(Rows.Count, "A").End(xlUp).Row, add one for the destination row, and use that cell as the Copy destination. Adjust the logic if the sheet has a header or the chosen column can contain blanks.

Does Range.Copy copy column widths?
Not as part of an ordinary range copy. It copies cell contents and formatting, but set column widths separately if the destination must match the source’s layout.

Can I run an Excel VBA copy macro in Excel for the web?
No. Excel for the web can retain a workbook with macros, but VBA must be run in desktop Excel. Office Scripts may suit some web-based automation needs, but it is a separate feature and its code is not interchangeable with VBA.

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.