Excel VBA Copy and Paste a Range to Another Worksheet

To copy a range to another worksheet with Excel VBA, identify the source and destination worksheets, then use the source range’s Copy method with the destination range. The example below copies cells A1:D20 from a worksheet named Source to cell A1 on Report.

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

    ' Use the worksheets in the workbook that contains this macro.
    Set sourceSheet = ThisWorkbook.Worksheets("Source")
    Set destinationSheet = ThisWorkbook.Worksheets("Report")

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

Change the sheet names and range addresses to match your workbook. This method copies the cells to the destination without requiring a separate paste command.

How the Copy Destination Method Works

Range.Copy can take a destination range, so VBA can copy a block directly rather than selecting cells, copying them, and activating another worksheet. In the example, A1 on Report is the top-left destination cell. Excel places the copied block starting there; the destination does not need to be written as a range the same size as the source.

Copying cells is different from assigning their values. A normal range copy carries cell contents and formulas along with formatting, much like copying and pasting cells in Excel. Formulas are adjusted according to Excel’s usual relative and absolute reference rules. For example, a relative reference may shift when its formula is copied to a different location, while a reference marked with dollar signs remains fixed.

The method does not require Select or Activate. Avoiding those calls makes code less dependent on which sheet or cell is currently selected and can make a macro easier to maintain. It also avoids unnecessary screen movement in many routines.

Choose What the Destination Should Receive

Use a full copy when the destination should resemble the source cells, including their formulas and formatting. If you only want the calculated results, copying everything may bring along unwanted formulas, number formats, or other cell properties. Choose the transfer method according to the intended result:

Formulas and formatting: Use Copy Destination for a normal cell copy.
Values only: Assign the source range’s Value to an equally sized destination range.
Selected paste content: Use Copy followed by PasteSpecial, such as a values-only paste.

For a values-only transfer, the following procedure writes the current cell values into the matching destination cells. It does not transfer the source formulas or formatting.

Sub CopyValuesToAnotherSheet()
    Dim sourceSheet As Worksheet
    Dim destinationSheet As Worksheet

    ' Use the worksheets in the workbook that contains this macro.
    Set sourceSheet = ThisWorkbook.Worksheets("Source")
    Set destinationSheet = ThisWorkbook.Worksheets("Report")

    ' The source and destination ranges have the same dimensions.
    destinationSheet.Range("A1:D20").Value = _
        sourceSheet.Range("A1:D20").Value
End Sub

For that assignment, both ranges are 20 rows by 4 columns. The destination receives the source cells’ values, including displayed results of formulas, rather than the formulas themselves. Existing destination formatting remains in place. If a source value is a date or currency amount, the underlying value is transferred but the destination’s number format determines how it appears.

Use PasteSpecial for a Specific Paste Type

When you need a paste option that is not covered by a values assignment, use PasteSpecial. For example, this procedure copies a range and pastes only its values at A1 on Report:

Sub PasteValuesOnly()
    Dim sourceSheet As Worksheet
    Dim destinationSheet As Worksheet

    ' Use the worksheets in the workbook that contains this macro.
    Set sourceSheet = ThisWorkbook.Worksheets("Source")
    Set destinationSheet = ThisWorkbook.Worksheets("Report")

    ' Copy first, then paste only values at the destination.
    sourceSheet.Range("A1:D20").Copy
    destinationSheet.Range("A1").PasteSpecial Paste:=xlPasteValues

    ' Clear Excel's copy marquee and end copy mode.
    Application.CutCopyMode = False
End Sub

The Excel constant xlPasteValues specifies a values-only paste. Other paste types are available, but use the one that matches your goal rather than assuming every paste option transfers the same properties. A separate paste operation uses Excel’s copy mode, so clearing Application.CutCopyMode afterward removes the moving copy border. It is not necessary for the direct Copy Destination example.

Copy Between Workbooks

If the source and destination sheets are in different open workbooks, qualify each worksheet through its workbook. ThisWorkbook means the workbook containing the VBA code; it does not automatically mean the workbook a user happens to be viewing. This distinction matters when the macro is stored in Personal.xlsb or an add-in.

For example, if both files are open and their names are Sales.xlsx and Summary.xlsx, set workbook variables before accessing the worksheets:

Sub CopyBetweenOpenWorkbooks()
    Dim sourceBook As Workbook
    Dim destinationBook As Workbook
    Dim sourceSheet As Worksheet
    Dim destinationSheet As Worksheet

    ' These workbooks must already be open, with these names.
    Set sourceBook = Workbooks("Sales.xlsx")
    Set destinationBook = Workbooks("Summary.xlsx")
    Set sourceSheet = sourceBook.Worksheets("Data")
    Set destinationSheet = destinationBook.Worksheets("Report")

    ' Copy the source block to the destination worksheet.
    sourceSheet.Range("A1:D20").Copy _
        Destination:=destinationSheet.Range("A1")
End Sub

Workbook names in the Workbooks collection must match the open workbook names, including the file extension. If a required workbook is closed, this example cannot find it. Open the file first or adapt the macro to open it using a known path. Save changes to the destination workbook if you need the copied data to remain after closing it.

Adapt the Source and Destination Ranges

For a fixed-size report, a clear address such as A1:D20 is often easiest to read. If the amount of data changes, calculate the last populated row and build the source range accordingly. The method should reflect the structure of the data: a last-row calculation based on column A assumes that column A is populated for every record that should be copied.

To copy a variable number of rows from A1:D through the last non-empty cell in column A, use a pattern like this:

Sub CopyVariableRows()
    Dim sourceSheet As Worksheet
    Dim destinationSheet As Worksheet
    Dim lastRow As Long

    ' Use the worksheets in the workbook that contains this macro.
    Set sourceSheet = ThisWorkbook.Worksheets("Source")
    Set destinationSheet = ThisWorkbook.Worksheets("Report")

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

    ' Copy columns A through D from row 1 to the last row.
    sourceSheet.Range("A1:D" & lastRow).Copy _
        Destination:=destinationSheet.Range("A1")
End Sub

This common last-row approach returns row 1 if column A has no entries, because the search moves upward from the bottom of the column. Decide whether that is acceptable for your workbook; if a header-only sheet should not be copied, add a check for the expected minimum row before copying. Also consider whether formulas that return an empty string count as populated for the way your data is organized.

Run and Save the Macro Safely

In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11, insert a standard module, and place the procedure in it. Run it from the editor or use Developer > Macros, select the macro, and choose Run. If the Developer tab is not visible, enable it through Excel’s ribbon customization options.

Save a copy of the workbook before running a macro. VBA changes cannot be undone with Ctrl+Z, and a macro can overwrite destination cells or change a large range quickly. Save a workbook containing VBA in a macro-enabled format such as .xlsm if you want the code to remain in that file. Enable macros only for workbooks you trust.

Excel for Mac supports VBA, though keyboard shortcuts can vary with the keyboard and function-key settings. The Visual Basic Editor is available through Excel’s Tools > Macro menu in supported versions; a function-key modifier may be needed on some keyboards. Excel for the web can open macro-enabled workbooks, but it cannot create, edit, or run VBA macros. Use the desktop application to run this code.

Common Problems and Their Fixes

“Subscript out of range”: Check that each worksheet name is spelled exactly as it appears on the tab and that the referenced workbook is open. Worksheet names are not interchangeable with workbook names.
The wrong workbook is changed: Replace an unintended ActiveWorkbook reference with an explicit workbook variable or ThisWorkbook, depending on where the macro is stored.
Values appear but formatting does not: That is expected when using a values assignment or xlPasteValues. Use a normal copy if formatting should also be transferred.
A date displays as a number: A values-only transfer preserves the underlying date value but not the source number format. Apply a suitable date format to the destination range.
The macro stops on a protected sheet: Check whether the destination sheet is protected and whether the intended edit is permitted. Do not assume a macro can bypass worksheet protection.
Only part of the expected data is copied: Confirm the source address and any last-row calculation. A dynamic range based on one column will miss records if that column has gaps.

Merged cells can also complicate copying because the source and destination layouts may not match. If a copy raises an error or produces an unexpected layout, inspect the merged areas and test with a small range first. When the purpose is to populate a clean report, it is often simpler to use an unmerged destination layout.

Frequently Asked Questions

How do I copy a range to the next worksheet?
Set the source worksheet and destination worksheet explicitly, then use Copy Destination with the top-left destination cell. Naming the target sheet is more reliable than relying on whichever sheet is active.

Does VBA copy formulas as well as values?
A normal range copy transfers formulas as formulas, along with cell formatting. A values assignment or a paste using xlPasteValues transfers results instead of formulas.

How can I copy a range without formatting?
Assign the source range’s Value to a same-sized destination range, or use Copy followed by PasteSpecial Paste:=xlPasteValues. The first option avoids copy mode; the second uses Excel’s paste mechanism.

Can I copy a range to a worksheet in another workbook?
Yes. Reference the source and destination sheets through their respective workbook objects, and make sure both workbooks are open when the macro uses the Workbooks collection.

Why can’t I undo a VBA copy with Ctrl+Z?
Running VBA clears Excel’s ordinary undo history for the macro’s changes. Save a separate copy before running code that may overwrite data; Ctrl+Z cannot reverse the macro operation.

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.