Excel VBA Copy: How to Copy Cells, Sheets, and Files

To copy cells with Excel VBA, use Range.Copy with a destination, such as Worksheets("Sales").Range("A1:D20").Copy Destination:=Worksheets("Summary").Range("A1"). This copies the cells’ formulas or values and formatting; use a direct value assignment instead when you want results without formulas.

What “copy” means in Excel VBA

Excel VBA can copy different things: a cell range, selected cell properties, an entire worksheet, or a workbook file. The right method depends on what should be reproduced and where it should go. Copying a range is the everyday case, but a range copy and a file backup are not interchangeable operations.

For a range, Copy behaves much like copying cells with the clipboard in Excel. It transfers cell content and associated cell-level features such as formatting, while formulas are copied as formulas. Excel may adjust relative references in formulas as they move, just as it does during a manual copy. It does not mean that every aspect of the source layout, such as column widths, is automatically duplicated.

In examples below, ThisWorkbook means the workbook containing the VBA code. Using named workbook and worksheet variables makes the destination explicit and helps avoid copying to whichever sheet happens to be active.

Copy a range to another worksheet

Use the Destination argument when copying cells to a location in the same workbook. It identifies the top-left cell of the destination area; Excel fills an area corresponding to the copied range. The source and destination ranges therefore do not need to be selected first.

Sub CopySalesRange()
    Dim wb As Workbook
    Dim wsSource As Worksheet
    Dim wsTarget As Worksheet

    ' Save a backup first; macro changes cannot be undone with Ctrl+Z.
    Set wb = ThisWorkbook
    Set wsSource = wb.Worksheets("Sales")
    Set wsTarget = wb.Worksheets("Summary")

    ' Copy A1:D20, including formulas and cell formatting.
    wsSource.Range("A1:D20").Copy Destination:=wsTarget.Range("A1")
End Sub

Before running a macro, save a separate copy of the workbook. A VBA procedure’s changes generally cannot be reversed with Excel’s Undo command (Ctrl+Z on Windows; Command+Z on Mac). To run this example, the workbook needs worksheets named exactly Sales and Summary. Change those names and the range addresses to match your file.

If the target cells already contain information, the copy replaces the corresponding cells. If you only want to copy one column or a smaller block, change the source address; for example, Range("B2:B50") copies 49 cells. The destination is still the first cell of the receiving area.

Copy values, formulas, or formats only

A full range copy is not always what a workflow needs. Assigning one range’s Value to another range’s Value transfers the displayed results as values rather than copying the formulas that produced them. The source and destination ranges should have matching dimensions.

Sub CopyValuesOnly()
    Dim wb As Workbook
    Dim wsSource As Worksheet
    Dim wsTarget As Worksheet

    ' Save a backup first; macro changes cannot be undone with Ctrl+Z.
    Set wb = ThisWorkbook
    Set wsSource = wb.Worksheets("Sales")
    Set wsTarget = wb.Worksheets("Summary")

    ' Transfer the results in A1:D20, not the formulas.
    wsTarget.Range("A1:D20").Value = wsSource.Range("A1:D20").Value
End Sub

This is often preferable for a fixed report or an exported snapshot that should not recalculate when the source changes. It transfers values, including text, numbers, dates stored as numbers, and cell errors. The destination’s existing number formats remain in place, so a date value may appear as a serial number if the receiving cells are formatted as General.

To transfer formulas while leaving destination formatting alone, assign the source’s Formula property to the destination’s Formula property. If only formatting is needed, the clipboard-based Copy and PasteSpecial pattern can be used:

Sub CopyFormatsOnly()
    Dim wb As Workbook
    Dim wsSource As Worksheet
    Dim wsTarget As Worksheet

    ' Save a backup first; macro changes cannot be undone with Ctrl+Z.
    Set wb = ThisWorkbook
    Set wsSource = wb.Worksheets("Sales")
    Set wsTarget = wb.Worksheets("Summary")

    ' Copy formatting without replacing destination values or formulas.
    wsSource.Range("A1:D20").Copy
    wsTarget.Range("A1:D20").PasteSpecial Paste:=xlPasteFormats
    Application.CutCopyMode = False
End Sub

Paste Special can also copy other specific attributes, such as formulas or column widths, using the corresponding Excel VBA paste constants. Formatting-only paste is useful when a destination has its own content but should adopt the source’s appearance. Clear the copy mode afterward to remove Excel’s moving border and clipboard state.

Copy a worksheet within a workbook

To duplicate a whole worksheet, call the worksheet’s Copy method and specify where the new sheet should appear. For example, this macro places a copy of Report after Data, then gives it a new name:

Sub DuplicateReportSheet()
    Dim wb As Workbook
    Dim wsCopy As Worksheet

    ' Save a backup first; macro changes cannot be undone with Ctrl+Z.
    Set wb = ThisWorkbook

    ' Insert the duplicate immediately after the Data sheet.
    wb.Worksheets("Report").Copy After:=wb.Worksheets("Data")
    Set wsCopy = wb.ActiveSheet
    wsCopy.Name = "Report Copy"
End Sub

The new name must not already be used by another sheet in that workbook, and the referenced source and destination sheets must exist. If you omit both Before and After, Excel creates a new workbook containing the copied sheet instead. That default can surprise a macro writer who expected the duplicate to remain in the original file.

A worksheet copy is broader than copying its used cell range: it duplicates the sheet as a sheet, rather than merely transferring a rectangle of cells. Still, if the goal is to make a clean data-only export, copying a range and pasting values into a prepared sheet may be more appropriate than duplicating the entire worksheet.

Make a copy of a workbook file

If “copy” means create a backup file of the current workbook, use SaveCopyAs. It writes a copy to another path without switching the open workbook to that new file. The destination folder must be available, and you should choose a distinct filename so you do not accidentally replace a backup you intended to keep.

Sub SaveWorkbookCopy()
    Dim wb As Workbook
    Dim backupPath As String

    ' Save a separate backup before running this macro.
    Set wb = ThisWorkbook

    ' A never-saved workbook has no folder path yet.
    If Len(wb.Path) = 0 Then
        MsgBox "Save the workbook before creating a backup."
        Exit Sub
    End If

    backupPath = wb.Path & Application.PathSeparator & "Backup_" & wb.Name
    wb.SaveCopyAs Filename:=backupPath
End Sub

This example uses the same folder as the original and prefixes the filename with Backup_. It preserves the workbook’s current file format, so a macro-enabled workbook remains in its macro-enabled format. If you need a copy in a different format, use an appropriate save workflow and check the resulting file type rather than assuming SaveCopyAs converts formats.

Copy between workbooks

For a range transfer between two workbooks, qualify both the source and destination with workbook and worksheet references. The destination workbook generally needs to be open for this direct range-copy pattern. Avoid relying on ActiveWorkbook unless the macro deliberately works with whichever workbook is active; the active workbook can change as users interact with Excel.

For example, the same range-copy statement can use a variable for each workbook: wbSource.Worksheets("Sales").Range("A1:D20").Copy Destination:=wbTarget.Worksheets("Summary").Range("A1"). In a complete macro, declare both variables as Workbook and set them to the intended open workbooks before that statement. The target sheet and range must be valid, just as they must be for a same-workbook copy.

If the purpose is only to create a file backup, use SaveCopyAs instead of opening another workbook and copying every sheet manually. If the purpose is to move a sheet to a separate workbook, the worksheet Copy method can create or target another workbook, but be deliberate about whether you want a new workbook or a sheet inserted into an existing one.

Common copy errors and safer habits

Many copy errors come from workbook context rather than from the Copy method itself. A macro that refers to Range("A1") without a worksheet qualifier uses the active sheet, which may not be the intended one. Qualifying ranges as wsSource.Range("A1") and wsTarget.Range("A1") makes the operation easier to understand and maintain.

“Subscript out of range”: A worksheet or workbook name in the code does not match an available object. Check spelling, spaces, and which workbook contains the sheet.
Unexpected formula changes: Relative references may adjust when cells are copied. Use value assignment if you need fixed results rather than formulas.
Destination content disappears: A normal copy overwrites the cells it covers. Confirm the target range before running the procedure.
Formatting or dates look wrong: A values-only transfer keeps the destination’s formatting. Apply the required number format separately if appearance matters.
Copy mode remains active: After a clipboard-based copy or paste, set Application.CutCopyMode = False when the operation is complete.

Test a macro on a disposable copy of the workbook, especially if it loops through many sheets or overwrites a large area. VBA changes are not undone through the normal Undo history, so a saved backup is a practical safety measure, not just a precaution for complex code.

VBA copying versus manual copying

Manual copying is convenient for a one-time task: select the range, press Ctrl+C and Ctrl+V on Windows, or Command+C and Command+V on Mac. VBA is useful when the same transfer must happen repeatedly, when ranges depend on a process, or when copying is one step in a larger macro.

Macros can save repetitive work, but they also make it easier to overwrite data at scale. For a one-off transfer, manual copy and Paste Special may be simpler and easier to inspect. For a recurring task, a clearly named macro with explicit source and destination references is more consistent. Excel for the web can open many macro-enabled workbooks, but it cannot run VBA macros; use desktop Excel for Windows or Mac to execute VBA.

Frequently Asked Questions

How do I copy a range to another sheet in Excel VBA?
Qualify the source and target worksheets, then use sourceRange.Copy Destination:=targetRange. The destination range should begin at the top-left cell where the copied block belongs.

How do I copy cells without formulas?
Assign the source range’s Value property to the destination range’s Value property. This transfers the calculated results rather than the formulas and leaves destination formatting in place.

Can VBA copy cells between two workbooks?
Yes. Reference the source and destination workbooks and worksheets explicitly. For a direct range copy, the workbooks should be open and the destination sheet must exist.

Why can’t I undo an Excel VBA copy?
Running a macro clears or disrupts Excel’s regular Undo history, so Ctrl+Z or Command+Z is not a dependable way to reverse its changes. Save a separate workbook copy before running a macro that changes data.

Can Excel for the web run VBA?
No. Excel for the web can work with many macro-enabled files, but VBA macros must be run in the desktop Excel application.

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.