VBA Code to Copy and Paste Data in Excel: Examples

To copy a range to another worksheet with VBA, use Range.Copy and specify the destination: sourceSheet.Range("A1:D20").Copy Destination:=destinationSheet.Range("A1"). This copies the cells, including their formulas and formatting; for values only, assign the source range’s .Value to an equally sized destination range.

The basic VBA copy-and-paste pattern

In Excel for Microsoft 365 on Windows, a VBA copy operation identifies a source range and a destination. The code below copies cells A1 through D20 from a worksheet named Data to a worksheet named Report, starting at A1. Change the sheet names and range to match your workbook.

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

    ' Refer to sheets in the workbook containing this macro.
    Set sourceSheet = ThisWorkbook.Worksheets("Data")
    Set destinationSheet = ThisWorkbook.Worksheets("Report")

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

The Destination argument tells Excel where the upper-left cell of the copied range should go. It does not need a separate Paste command. The destination area must be large enough for the copied cells; Excel fills the corresponding rows and columns. If the destination already contains information, the copied cells replace it in the overlapping area.

ThisWorkbook means the workbook that contains the macro. That is often preferable to ActiveWorkbook, which refers to whichever workbook is active at the time the code runs. Referring to the intended workbook and worksheets by name reduces the risk of copying to the wrong file.

Where to put the macro and how to run it

Save a copy of the workbook before running any macro. VBA actions cannot be undone with Ctrl+Z, so a backup gives you a way to recover if a range is overwritten or the code targets the wrong sheet. If you intend to keep the macro in the workbook, save it as an Excel Macro-Enabled Workbook (.xlsm); the standard .xlsx format does not store VBA code.

Open the workbook and press Alt+F11 to open the Visual Basic Editor on Windows. On Mac, Option+F11 may open it; depending on keyboard settings, you may need Fn as well. You can also use Tools > Macro > Visual Basic Editor on Mac.
In the editor, choose Insert > Module, then paste the procedure into the new module.
Return to Excel and press Alt+F8, select the macro, and choose Run. You can also run the procedure from the Visual Basic Editor with the cursor inside it by pressing F5.

If the Developer tab is hidden in Windows Excel, enable it through File > Options > Customize Ribbon and select Developer. On Mac, ribbon options are available from Excel > Settings > Ribbon & Toolbar. Only enable macros in files you trust; a macro can change workbook data and perform other actions.

Copying a range whose size changes

A fixed address such as A1:D20 is easy to understand, but it misses new records added below row 20. To copy a changing list, find its last used row in a column that is filled for every record, then build the range using that row number. This example assumes row 1 contains headings, the records are in columns A through D, and column A has no gaps in the data.

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

    Set sourceSheet = ThisWorkbook.Worksheets("Data")
    Set destinationSheet = ThisWorkbook.Worksheets("Report")

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

    ' Copy the heading and all rows through the last record.
    sourceSheet.Range("A1:D" & lastRow).Copy _
        Destination:=destinationSheet.Range("A1")
End Sub

The expression starting at the bottom of column A and using End(xlUp) is the VBA equivalent of moving upward from the bottom of that column to its last non-empty cell. If column A can be blank in some records, choose a more consistently populated column instead. This method identifies the last cell in one column, not necessarily the last used cell anywhere on the worksheet.

If the source sheet might contain no records, add a check before copying. A worksheet with no entries in column A still returns row 1 from this last-row pattern, so a macro that must distinguish an empty sheet from a sheet with a heading should check the contents of the relevant cells explicitly.

Copying values without formulas or formatting

Use a direct value assignment when the destination should receive the displayed results of formulas rather than the formulas themselves. It also avoids using the clipboard. The destination range is resized to match the source range, so the two ranges have the same number of rows and columns.

Sub CopyValuesOnly()
    Dim sourceSheet As Worksheet
    Dim destinationSheet As Worksheet
    Dim sourceRange As Range
    Dim destinationRange As Range

    Set sourceSheet = ThisWorkbook.Worksheets("Data")
    Set destinationSheet = ThisWorkbook.Worksheets("Report")
    Set sourceRange = sourceSheet.Range("A1:D20")
    Set destinationRange = destinationSheet.Range("A1").Resize( _
        sourceRange.Rows.Count, sourceRange.Columns.Count)

    ' Transfer cell values, not formulas or formatting.
    destinationRange.Value = sourceRange.Value
End Sub

For example, if a source cell contains a formula that currently returns 25, this assignment puts the value 25 in the destination cell, not the formula. It does not copy font, fill, borders, column widths, or number formats. A date may therefore arrive as its underlying numeric value and display differently if the destination has a different number format. Apply an appropriate format separately if the appearance matters.

Direct assignment is usually a good fit for consolidating results, exporting a snapshot, or moving values without changing the clipboard. It does not reproduce every aspect of a normal copy operation, such as cell formatting or validation settings. Choose the method according to what must be preserved.

Choosing between full copy, values, and Paste Special

There is no single best copy method for every task. Decide whether the destination needs formulas, formatting, or only the current cell contents before writing the macro. These approaches behave differently:

Copy with Destination: Use Range.Copy Destination:=... when the destination should receive the copied cells with their formulas and formatting. This is the most direct option for a normal range copy.
Assign .Value: Use destinationRange.Value = sourceRange.Value when you need values only and want to avoid a clipboard operation. Formula results are transferred as values.
Paste Special: Use Copy followed by PasteSpecial when you need a particular subset, such as values and number formats. This uses Excel’s copy-and-paste mechanism and makes the desired paste behavior explicit.

For example, the following procedure pastes values and number formats from A1:D20 to A1 on the Report sheet. It does not paste the source formulas as formulas.

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

    Set sourceSheet = ThisWorkbook.Worksheets("Data")
    Set destinationSheet = ThisWorkbook.Worksheets("Report")

    ' Copy, then paste values and number formats only.
    sourceSheet.Range("A1:D20").Copy
    destinationSheet.Range("A1").PasteSpecial _
        Paste:=xlPasteValuesAndNumberFormats

    ' Clear the moving-copy outline and copy mode.
    Application.CutCopyMode = False
End Sub

Paste Special options are available through Excel’s VBA constants, including xlPasteValues, xlPasteFormats, and xlPasteFormulas. Pick the one that matches the outcome you need rather than assuming “paste” always means values only.

Appending copied records below existing data

Copying to A1 replaces data already there. To add records after the existing entries, calculate the next available row on the destination sheet. This example skips the source heading row and appends rows 2 through the last record in columns A through D. It assumes column A is populated for each destination record and that the destination’s headings are already in row 1.

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

    Set sourceSheet = ThisWorkbook.Worksheets("Data")
    Set destinationSheet = ThisWorkbook.Worksheets("Report")

    lastSourceRow = sourceSheet.Cells(sourceSheet.Rows.Count, "A").End(xlUp).Row

    ' Stop when the source has headings but no records.
    If lastSourceRow < 2 Then Exit Sub

    ' Find the first empty row in the destination's column A.
    If Application.CountA(destinationSheet.Columns("A")) = 0 Then
        nextDestinationRow = 1
    Else
        nextDestinationRow = _
            destinationSheet.Cells(destinationSheet.Rows.Count, "A").End(xlUp).Row + 1
    End If

    ' Append records without copying the source heading.
    sourceSheet.Range("A2:D" & lastSourceRow).Copy _
        Destination:=destinationSheet.Cells(nextDestinationRow, 1)
End Sub

Because this version copies formulas and formatting as well as cell contents, decide whether that is appropriate for the destination. If you want to append values only, use the same row calculations but assign the source range’s .Value to a destination range resized to the source’s dimensions. Also consider what should happen if the destination has gaps in column A: the code finds the last occupied row, not the first empty gap.

Common errors and practical safeguards

Most copy macros fail because a reference or assumption does not match the workbook. Check the worksheet names character for character, including spaces. A reference such as Worksheets("Report") produces an error if the tab is named “Monthly Report.” If a sheet might not exist, add error handling or validate the workbook setup rather than silently changing a sheet reference.

Wrong workbook: Use ThisWorkbook when the macro is stored in the workbook it should edit. Be cautious with ActiveWorkbook if several files are open.
Wrong last row: The last-row calculation only checks its chosen column. Select a column that reliably contains a value on each record.
Data overwritten: Confirm the destination address before running the macro. A paste beginning at A1 can replace existing cells in the copied range.
Unexpected formulas or appearance: Decide whether formulas, values, number formats, and other formatting should be transferred; select the copy method accordingly.
Macro not available: Check that the file is saved as .xlsm if the code needs to remain in the workbook, and confirm that the procedure is in a standard module.

Macros cannot be reversed with Ctrl+Z on Windows or Command+Z on Mac. Save a backup before running code that changes a workbook, and try new code on a disposable copy first. Avoid enabling macros in unexpected files or lowering Excel’s macro security settings just to run code from an unknown source.

Frequently Asked Questions

How do I copy and paste a range in Excel VBA?
Set worksheet variables, then use sourceSheet.Range("A1:D20").Copy Destination:=destinationSheet.Range("A1"). Replace the sheet names and range with those in your workbook. This copies the range’s formulas and formatting as well as its contents.

How do I paste values only with VBA?
Set source and destination ranges of the same size, then assign destinationRange.Value = sourceRange.Value. This transfers current values, including formula results, but not the formulas or formatting.

Can VBA copy data to another workbook?
Yes. Set a Workbook variable for the other open workbook and a Worksheet variable for its destination sheet, then use the same copy pattern. Make sure the intended workbook is open and use explicit workbook references to avoid relying on whichever file happens to be active.

How do I copy only the used rows?
Find the last populated row in a reliable column with Cells(Rows.Count, column).End(xlUp).Row, then use that row number to build the source range. This finds the end of that column’s data; it does not automatically detect a perfect rectangular data region when columns contain gaps or extra notes.

Why can’t I undo a VBA paste?
Running a VBA macro clears Excel’s normal undo history for its changes, so Ctrl+Z cannot reverse the macro. Save a separate copy before running code that modifies data, and check the source and destination references first.

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.