VBA Copy and Paste Data Between Excel Sheets

To copy a range from one Excel worksheet to another with VBA, use a macro that names both sheets and copies the source range to the destination’s top-left cell. For example, sourceSheet.Range("A1:D20").Copy Destination:=targetSheet.Range("A1") copies that block, including its formulas and formatting; the full macro and useful variations are below.

How copying between worksheets works

A worksheet is an object in Excel’s VBA model, and a cell or block of cells is a Range. A copy macro identifies the worksheet that holds the source data, identifies the worksheet that should receive it, and specifies both the range to copy and where the pasted block should begin. Excel fills the destination in the same shape as the source: copying four columns by twenty rows to A1 fills A1:D20.

It is important to distinguish copying from moving. Copying leaves the original cells in place. If the goal is to relocate data, you can copy it and then clear the source, but that is a separate operation and should be done carefully. A macro can overwrite existing destination cells without displaying the same prompts a person may see while working manually, so check the target range before running it.

When you provide the destination as the Destination argument to Copy, Excel copies directly to that location. This is usually simpler than calling Copy and then Paste, and it avoids relying on the active worksheet or a later clipboard action.

Copy a fixed range with a complete macro

This example copies cells A1:D20 from a worksheet named Data to a worksheet named Archive in the workbook that contains the macro. Change the sheet names and range to match your file. The sheet names must match the worksheet tabs, including spaces and punctuation.

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

    ' Use the workbook that contains this VBA code.
    Set sourceSheet = ThisWorkbook.Worksheets("Data")
    Set targetSheet = ThisWorkbook.Worksheets("Archive")

    ' Copy A1:D20 to A1 on the destination sheet.
    sourceSheet.Range("A1:D20").Copy _
        Destination:=targetSheet.Range("A1")
End Sub

The line break and underscore in the copy statement are VBA’s line-continuation syntax. You can also put that statement on one line. Because the example refers to each worksheet explicitly, it does not depend on which sheet is currently selected. That makes it more reliable than code using Selection, ActiveSheet, or repeated calls to Activate.

ThisWorkbook means the workbook containing the VBA code. If the macro is stored in a different workbook, such as Personal.xlsb, then ThisWorkbook may not be the workbook containing Data and Archive. In that case, set a Workbook variable to the intended workbook and use that variable to refer to its worksheets.

Copy a changing number of rows

A fixed range is suitable when the data always has the same size. For a list that grows over time, find the last used row and build the range from that result. This example assumes row 1 contains headings, the records occupy columns A:D, and every record has a value in column A.

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

    Set sourceSheet = ThisWorkbook.Worksheets("Data")
    Set targetSheet = ThisWorkbook.Worksheets("Archive")

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

    ' Copy the header and records when the sheet has content.
    If lastRow >= 1 Then
        sourceSheet.Range("A1:D" & lastRow).Copy _
            Destination:=targetSheet.Range("A1")
    End If
End Sub

The expression using Rows.Count starts at the bottom of column A and moves upward to the first non-empty cell. If column A can be blank on a valid record, it is not a reliable column for finding the end of the list; choose a column that is consistently populated, or use a different method that checks the whole data area. Also consider what should happen when the source is empty. As written, the macro does nothing if the last used row is zero, but if row 1 contains headings, it copies the headings even when there are no records.

This version does not clear older content below the new data on Archive. For example, if the previous copy had 100 rows and the new one has 80, rows 81 through 100 may remain. Decide whether that is desirable before adding a clear operation; clearing an entire destination column could also erase notes or formulas that should be kept.

Copy values without formulas or formatting

The Copy Destination approach transfers cell content and much of the cell-level presentation, including formulas and formatting. Sometimes the destination should contain only the displayed results, not formulas that refer back to the source. In that situation, assign the source range’s Value to a same-sized destination range:

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

    Set sourceSheet = ThisWorkbook.Worksheets("Data")
    Set targetSheet = ThisWorkbook.Worksheets("Archive")

    ' Put the source results into the matching destination cells.
    targetSheet.Range("A1:D20").Value = _
        sourceSheet.Range("A1:D20").Value
End Sub

This copies values, including the current results of formulas, but does not copy the source formatting. If the source has a formula such as a total, the destination receives the calculated result rather than the formula itself. The destination’s existing number formats remain in place, so a date or currency value may look different if the destination cells use a different format. The source and destination ranges should have matching dimensions for this direct assignment.

For a manual operation, Excel also offers Paste Special choices. VBA can use PasteSpecial after copying, but direct value assignment is often clearer when the requirement is simply to transfer values. Use the full copy method when formulas, cell formatting, or other copied cell attributes matter.

Run the macro safely

Before running a macro, save a separate copy of the workbook. VBA changes cannot be reversed with Ctrl+Z, and a macro may overwrite destination cells immediately. Keep the original file until you have checked the result.

Save the workbook in a macro-enabled format such as Excel Macro-Enabled Workbook (.xlsm) if it needs to retain VBA code.
Open the Visual Basic Editor. On Windows, press Alt+F11. In the editor, choose Insert > Module, then paste the code into the module.
Check that the worksheet names and range references match the workbook. Save the file.
Run the macro from the editor with the cursor inside its procedure, or return to Excel and press Alt+F8, select the macro, and choose Run.
Inspect the destination sheet, including the first and last copied rows and any formulas or formatting that matter.

On Excel for Mac, use Tools > Macro > Visual Basic Editor to open the VBA editor; available keyboard shortcuts can vary with the keyboard and macOS settings. The VBA statements above are intended for desktop Excel on Windows and Mac. Excel for the web can open a workbook containing macros, but it cannot create, edit, or run VBA macros in the browser. Use the desktop app to run this code.

Common problems and how to fix them

“Subscript out of range”: A worksheet reference does not match a sheet in the workbook being referenced. Check spelling, spaces, and the workbook reference. A tab caption can also differ from a worksheet’s VBA code name.
The wrong workbook is used: ThisWorkbook refers to the workbook where the macro is stored, not necessarily the workbook currently active on screen. Store the code in the data workbook or explicitly reference the intended workbook.
Only some rows copy: The dynamic example finds the last record using column A. If the last records have empty cells in A, use a more consistently populated column or adapt the range-finding logic.
Old rows remain: Copying a shorter list does not erase content below it. Clear only the old data area if that is part of the intended process, and avoid clearing cells that contain unrelated information.
Nothing appears to happen: Confirm the macro ran without an error, then inspect the named destination sheet and cells. A hidden worksheet or a different workbook can make the result easy to miss.
Macros are blocked: Excel’s security settings may prevent code from running. Run macros only from a workbook you trust and follow your organization’s security policy; do not enable code from an unknown source simply to make a file work.

Another frequent misconception is that copying a range copies everything on the worksheet. It copies the specified cell range, not every object or feature on the sheet. Pictures, buttons, charts, comments, validation rules, and table behavior may need separate treatment depending on the exact operation and Excel object involved. Test a representative range before automating a larger workbook.

Choosing the right copying method

Choose the method based on what the destination should contain, not just on how many lines of code it takes. A direct range copy is appropriate when the destination should resemble the source. Value assignment is a good fit for a static snapshot. If only formulas, formats, or column widths are required, a more selective Paste Special operation may be appropriate.

Use Copy Destination when you want the copied cells’ formulas and formatting as well as their contents.
Use .Value = .Value when you want results without carrying formulas or source formatting.
Use a dynamic last-row approach when the number of records changes, after choosing a dependable column for detecting the end.
Use a table or a more tailored macro when the source is a structured Excel table or the job involves filtering, appending records, or handling multiple separate ranges.

For a one-time transfer, copying and pasting manually may be simpler. VBA becomes useful when the same operation is repeated, must follow a consistent rule, or is one step in a larger process. For automation that needs to work in Excel for the web, Office Scripts may be an alternative, but Office Scripts are not VBA and use a different programming model.

Frequently Asked Questions

How do I copy an entire worksheet to another sheet with VBA?
Copying a worksheet is different from copying a range. The examples here transfer cells into an existing destination sheet; duplicating a whole sheet uses the worksheet’s Copy method and creates another worksheet, rather than pasting into an existing one.

How can I paste data below existing data?
Find the next empty row on the destination using a column that is consistently filled, then use that row as the destination’s starting cell. Be sure to account for a header row and for an empty destination so the first record is not placed below an unintended blank row.

Does VBA copy formulas or their results?
Range.Copy Destination:=... copies formulas, so references may adjust as Excel copies them. Assigning .Value copies the current formula results instead. Choose based on whether the destination should remain linked to calculations.

Can I copy between two different workbooks?
Yes. Set a workbook variable for each workbook, then refer to the source and destination worksheets through those variables. Both workbooks need to be available to the macro, and the code must identify the correct open workbook rather than relying on the active window.

Why can’t I undo a VBA paste?
Running a macro clears Excel’s normal undo history, so Ctrl+Z cannot reverse its changes. Save a backup first, and test the macro on a copy or a small range before using it on important data.

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.