Excel Macro to Copy and Paste Data: A Practical Guide

To copy a data range between worksheets in Excel for Microsoft 365 on Windows, use a VBA macro with Range.Copy Destination:=. For example, a macro can copy rows from a sheet named Data to one named Archive; save a backup first, because VBA actions cannot be undone with Ctrl+Z.

What a copy-and-paste macro does

A macro is a set of VBA instructions that Excel runs for you. Instead of selecting a range, copying it, switching sheets, and choosing a destination each time, you run one procedure that repeats those actions. This is especially useful for routine imports, reports, and archiving when the source and destination locations follow a consistent pattern.

A standard range copy transfers the source cells’ contents and formatting. If a cell contains a formula, the copied cell normally contains a formula too, and relative references can adjust to the new location, just as they do when you copy and paste manually. The operation does not automatically make a permanent snapshot of formula results: formulas may recalculate in the destination workbook.

Copying a range is different from copying an entire worksheet. A range macro writes into a specified part of an existing sheet. It does not automatically reproduce the whole worksheet’s layout, sheet-level settings, or column widths. Decide whether you need the full cells, just their displayed results, or a complete worksheet before choosing a method.

Prepare the workbook and ranges

For the example below, the source sheet is named Data, the destination sheet is named Archive, and the data occupies columns A through D. Row 1 contains headings, so the macro copies from row 2 down. The last used row is determined by looking in column A. Change the sheet names and column range in the code to match your workbook exactly.

Confirm that both worksheet tabs exist and that their names match the code, including spaces.
Choose a destination cell that will not overwrite information you need to keep.
Check that column A is populated on every data row if you rely on it to find the last row.
Save a separate copy of the workbook before running a macro. Ctrl+Z cannot reverse a VBA operation.

Save a workbook containing VBA as an Excel Macro-Enabled Workbook (.xlsm) if you want to retain the macro. A normal .xlsx file does not store VBA code when saved in that format.

Example macro: copy cells to another sheet

In the Visual Basic Editor, insert a standard module and paste in this code. It copies all cells from A2:D in Data through the last non-empty cell in column A, placing the copied range at A2 in Archive. If there is no data below the heading, it displays a message and stops.

Option Explicit

Sub CopyDataToArchive()
    Dim sourceWs As Worksheet
    Dim destinationWs As Worksheet
    Dim lastRow As Long
    Dim sourceRange As Range

    ' Identify the worksheets in this workbook.
    Set sourceWs = ThisWorkbook.Worksheets("Data")
    Set destinationWs = ThisWorkbook.Worksheets("Archive")

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

    If lastRow < 2 Then
        MsgBox "There are no data rows to copy."
        Exit Sub
    End If

    ' Copy columns A through D, starting below the header.
    Set sourceRange = sourceWs.Range("A2:D" & lastRow)
    sourceRange.Copy Destination:=destinationWs.Range("A2")
End Sub

The macro uses ThisWorkbook, meaning the workbook that contains the code, rather than whichever workbook happens to be active. That reduces the chance of copying into the wrong open file. The destination begins at A2 each time, so a new run overwrites cells there that overlap the copied range. It does not first clear old destination rows. If a later source is shorter, leftover rows from an earlier run can remain below the newly copied data.

How to add and run the macro

In desktop Excel for Windows, open the workbook and press Alt+F11 to open the Visual Basic Editor. On a Mac, use Option+F11; depending on the keyboard, you may also need Fn.
In the editor, choose Insert > Module, then paste the macro into the module window.
Return to Excel and save the file as an .xlsm workbook if it is not already macro-enabled.
Run it from the Visual Basic Editor with the cursor inside the procedure, or return to Excel and press Alt+F8, select CopyDataToArchive, and choose Run. On Mac, Option+F8 opens the Macro dialog on supported keyboard layouts.

If Excel displays a security warning, only enable macros for a workbook you trust and understand. Macro security settings can prevent VBA from running, and workplace-managed computers may restrict changes. Avoid enabling all macros globally just to run one file; use the security options provided by your organization or Excel.

Copy values without formulas or formatting

Sometimes the destination should contain fixed results rather than formulas, and you may not want the source formatting. In that case, assign the source range’s Value property to a destination range of the same dimensions. The following alternative copies values only. It uses the same sheet names, row logic, and source range as the first example, so use it instead of—not in addition to—the earlier procedure if you want this behavior.

Option Explicit

Sub CopyValuesToArchive()
    Dim sourceWs As Worksheet
    Dim destinationWs As Worksheet
    Dim lastRow As Long
    Dim sourceRange As Range
    Dim destinationRange As Range

    ' Identify the worksheets in this workbook.
    Set sourceWs = ThisWorkbook.Worksheets("Data")
    Set destinationWs = ThisWorkbook.Worksheets("Archive")

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

    If lastRow < 2 Then
        MsgBox "There are no data rows to copy."
        Exit Sub
    End If

    Set sourceRange = sourceWs.Range("A2:D" & lastRow)
    Set destinationRange = destinationWs.Range("A2").Resize( _
        sourceRange.Rows.Count, sourceRange.Columns.Count)

    ' Transfer results without formulas or source formatting.
    destinationRange.Value = sourceRange.Value
End Sub

This direct assignment is usually a good fit for a values-only transfer within the same workbook. It does not copy the source cell formatting, formulas, or column widths. It also does not use the clipboard. The destination cells retain their existing formatting, so dates or large numbers may appear differently if the destination uses an unsuitable number format.

Handling changing data and destination rows

Using the last row makes the example adapt when the number of records changes. The method Cells(Rows.Count, "A").End(xlUp).Row starts at the bottom of column A and moves upward to its last non-empty cell. This is a common approach, but it depends on column A being a reliable indicator of a record. If column A can be blank while other columns contain data, the detected range may end too early. Choose a column that is consistently filled, or calculate the last row using a different rule suited to the layout.

The example always starts pasting at A2. To append records after existing entries instead, the macro must find the next available destination row and use that row as the starting cell. Appending is not the same as replacing: repeated runs can create duplicate records unless the macro also checks for records already transferred. Before adding append logic, decide how the macro should identify duplicates and what should happen when a record changes.

For a fixed-size block, such as A2:D50, a fixed range is simpler and easier to inspect. For regularly growing exports, a dynamic last-row check is more convenient. Excel Tables can also make growing data easier to manage, but a macro still needs a clear rule for which table rows to copy and where to put them.

Choose between full cells, values, and formulas

Use the method that matches the intended result, not just the one with the shortest code. A full cell copy is closest to ordinary copy and paste. A values-only assignment produces a snapshot of calculated results. Copying formulas separately is useful when the destination should continue calculating, but it requires attention to relative and absolute references.

Full cell copy: Use Range.Copy Destination:= when you want the copied cell contents and formatting together.
Values only: Use a .Value assignment when the destination should hold results rather than formulas and should keep its own formatting.
Formulas only: Use a formula-property assignment when calculations should be carried over without copying the source’s formatting. Check how references shift at the destination.
Whole worksheet: Consider copying a worksheet only when you need the sheet itself, not merely a block of records.

For example, a monthly report may need a values-only archive so that last month’s numbers remain fixed even if the source formulas change. A working model, by contrast, may need formulas so that outputs update when inputs change. Neither choice is universally safer; the right one depends on whether the destination is an archive or an active calculation area.

Common problems and safe macro habits

A “Subscript out of range” error commonly means Excel cannot find a worksheet with the name specified in the code. Check the worksheet tab spelling and confirm that the macro is in the workbook you expect. A different error can occur if the destination sheet is protected or if the target cells cannot be changed. Resolve the sheet or protection issue rather than repeatedly rerunning the macro.

Other problems are often caused by the shape of the data. If the last-row column contains gaps, the macro may miss records below the last filled cell in that column. If the destination is not empty, the macro may overwrite existing cells; if old data extends farther down than the new copy, stale rows may remain. Review both source and destination before running, particularly the first time you adapt a macro.

Test changes on a duplicate workbook with a small sample. Confirm the first and last copied rows, the destination start cell, and whether formulas or values are expected. Macros cannot be undone with Ctrl+Z, so a backup is more reliable than assuming the Undo command will restore the sheet. For recurring processes, document the source sheet, destination sheet, columns, and overwrite or append behavior near the code.

Excel for Mac and Excel for the web

VBA macros are supported in the desktop versions of Excel for Windows and Mac, though keyboard shortcuts and some security or editor details can differ. On Mac, use the Visual Basic Editor from Excel’s available developer tools if the shortcut is unavailable. On Windows, Alt+F11 opens the editor and Alt+F8 opens the Macro dialog.

Excel for the web does not create, edit, or run VBA macros. A workbook with VBA can be opened there, but running this procedure requires desktop Excel. If a workflow must run in a browser, investigate Office Scripts, which are a separate automation technology and use a different programming model; VBA code cannot simply be pasted into an Office Script.

Frequently Asked Questions

How do I copy data from one sheet to another with a macro?
Set worksheet variables, define the source range, and use sourceRange.Copy Destination:=destinationWs.Range("A2"). Replace the sheet names, range, and destination cell with the locations in your workbook.

How can I copy only values in Excel VBA?
Set a destination range with the same number of rows and columns, then assign destinationRange.Value = sourceRange.Value. This transfers results without carrying over the formulas or source formatting.

Can I undo a macro with Ctrl+Z?
No. Running VBA clears Excel’s normal Undo history, and Ctrl+Z does not reverse the macro’s changes. Save a separate copy before running a macro that changes workbook data.

Why does my macro say the worksheet name is invalid?
Check that the named worksheet exists in the workbook containing the macro and that its tab name matches the code exactly. Spaces and punctuation matter. The example uses ThisWorkbook, so it looks for those sheets in the workbook that stores the macro.

Can I run a VBA copy macro in Excel for the web?
No. Excel for the web does not run VBA macros. Open the workbook in desktop Excel for Windows or Mac, or consider Office Scripts if the process needs to run in the browser.

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.