Excel VBA Macro to Copy Data Between Sheets

VBA & macros · Updated November 19, 2026

To copy data between worksheets with an Excel VBA macro, assign the source range’s values to a destination range of the same size; use Excel’s Copy method instead when you also want formulas and formatting. The examples below show both approaches, explain how to run them safely, and cover common issues such as blank rows, old destination data, and Excel for the web.

What a copy macro does

A VBA macro automates actions in the desktop version of Excel. A copy macro identifies a source worksheet and range, identifies a destination worksheet and starting cell, then transfers the selected cells. You can run it again after updating the source instead of repeating the same selection and paste steps by hand.

“Copy data” can mean different things. A values-only transfer copies the current results displayed in cells, not the formulas that produced them. A full cell copy can bring formulas and formatting along as well. Decide which outcome you need before choosing a macro: transferring calculated results to a report is often best done with values, while duplicating a working table may call for formulas and formats.

The examples use a worksheet named Data as the source and Report as the destination. They assume the table starts in A1 and that the destination begins in A1. Change those sheet names or cells to match your workbook. Worksheet names in VBA must match the tab names exactly.

Choose values, formulas, or a full cell copy

  • Values only: Transfers the displayed results. The destination does not recalculate the original formulas. This is useful for static reports, exports, or snapshots.
  • Formulas and formatting: Uses Excel’s copy operation. Formulas usually adjust their relative references to suit the destination location, just as they do when copied manually.
  • Selected columns or rows: Copies only the fields you specify. This is useful when the source includes helper columns that should not appear in a report.

These choices are not interchangeable. For instance, if a source cell contains =SUM(B2:B10), a values-only transfer writes its calculated number to the destination. A full copy writes a formula, which may adjust its references depending on where it lands. If the source formula refers to another worksheet, confirm that the copied formula still points to the intended sheet and cells.

Prepare the workbook and open the VBA editor

Save a separate copy of the workbook before running a macro. Excel macros cannot be undone with Ctrl+Z, and an incorrect destination or clearing command can overwrite data. Keep a backup until you have checked the result.

  1. Save the file as an Excel Macro-Enabled Workbook (.xlsm) using File > Save As. A standard .xlsx file does not store VBA code.
  2. If the Developer tab is not visible in Excel for Microsoft 365 on Windows, open File > Options > Customize Ribbon, select Developer, and choose OK.
  3. Open the VBA editor with Alt+F11 on Windows. On a Mac, use Option+F11; depending on the keyboard, you may also need Fn. You can also use Developer > Visual Basic.
  4. In the editor, choose Insert > Module, then paste a macro into the new module.

Do not enable all macros indiscriminately. Run code only when you understand its purpose and trust where it came from. Excel for the web can open a workbook containing macros, but it cannot create, edit, or run VBA macros; use the desktop Excel app for these steps.

Macro to copy values from a table

This version copies the values in the contiguous table around A1 on the Data sheet and writes them starting at A1 on Report. It does not copy the source formatting or formulas. CurrentRegion identifies a block of cells bounded by blank rows and columns, so the table should not have fully blank separator rows or columns inside it.

Sub CopyTableValues()
    Dim sourceSheet As Worksheet
    Dim destinationSheet As Worksheet
    Dim sourceRange As Range
    Dim destinationCell As Range

    ' Set the worksheets and the table's starting point.
    Set sourceSheet = ThisWorkbook.Worksheets("Data")
    Set destinationSheet = ThisWorkbook.Worksheets("Report")
    Set sourceRange = sourceSheet.Range("A1").CurrentRegion
    Set destinationCell = destinationSheet.Range("A1")

    ' Write the source results into an equally sized destination range.
    destinationCell.Resize(sourceRange.Rows.Count, _
        sourceRange.Columns.Count).Value = sourceRange.Value
End Sub

To run it, return to Excel, select Developer > Macros, choose CopyTableValues, and click Run. You can also place the cursor inside the procedure in the VBA editor and press F5. In this example, if the source table occupies A1:C4, the macro writes its 4 rows and 3 columns of values into Report!A1:C4.

The macro does not remove anything outside the newly written range. If the report previously contained more rows or columns, those older cells remain. That behavior avoids erasing unrelated content, but it means you may need to clean up the destination deliberately. Check the destination before adding any code that clears cells.

Macro to copy formulas and formatting

For a normal Excel-style copy, use the Range.Copy method with a destination. This version copies the same contiguous table from Data!A1 and places it at Report!A1. Excel copies cell contents and formatting, including formulas; formula references may change as they do during a manual copy.

Sub CopyTableWithFormulasAndFormats()
    Dim sourceSheet As Worksheet
    Dim destinationSheet As Worksheet
    Dim sourceRange As Range
    Dim destinationCell As Range

    ' Identify the source table and destination.
    Set sourceSheet = ThisWorkbook.Worksheets("Data")
    Set destinationSheet = ThisWorkbook.Worksheets("Report")
    Set sourceRange = sourceSheet.Range("A1").CurrentRegion
    Set destinationCell = destinationSheet.Range("A1")

    ' Copy the range using Excel's standard cell-copy behavior.
    sourceRange.Copy Destination:=destinationCell
End Sub

In contrast with the values macro, this method can bring formulas and cell formatting into the destination. It does not automatically make the destination a synchronized mirror: running the macro again performs another copy at that time, but later edits to the source are not automatically reflected unless you run the macro again or build a separate update mechanism.

Adjust the source range and destination

If your data does not form one continuous table, do not rely on CurrentRegion. It stops at completely blank rows and columns, which is convenient for a simple table but can split data that contains intentional gaps. You can instead specify a fixed range, such as sourceSheet.Range("A1:D200"), or calculate the last row based on a column that is always filled. A fixed range is straightforward, but it may include unused rows or fail to include new ones as the table grows.

For a fixed source range, replace the line that sets sourceRange with the range you want, and change the destination cell independently. For example, setting the destination to Report.Range("B3") places the upper-left source cell at B3. The destination range expands to fit the source in the values-only macro; make sure that expanded area is clear of information you need to keep.

When copying only specific columns, define a source range for each block or copy the required columns individually. This is safer than copying a large rectangle and deleting unwanted columns afterward, especially if the destination has a carefully designed layout.

Common problems and how to avoid them

  • “Subscript out of range”: A worksheet name in the code does not match a tab name, or the sheet is in a different workbook. Check spelling, spaces, and the workbook containing the macro.
  • Only part of the table copies: CurrentRegion has stopped at a fully blank row or column. Remove unintended gaps or specify a range that includes the required cells.
  • Old rows remain: The macro writes the new data but does not clear leftover rows from a previous, larger copy. Compare the new and old sizes, then clear only the known output area if appropriate.
  • Results differ after copying: The values macro transfers results, whereas the full-copy macro transfers formulas that may recalculate or change relative references. Choose the method based on whether the destination should be a snapshot or remain formula-driven.
  • Nothing runs: Confirm that you are using desktop Excel, the file is saved as .xlsm, and you selected the correct macro. If Excel blocks macros, follow your organization’s security guidance rather than lowering macro protection for all files.

A common misconception is that copying formulas makes two sheets permanently linked. It does not. A copied formula may refer back to the original sheet, but that is a formula reference, not automatic synchronization of every cell and formatting change. If you need a refreshed report, run the macro again or use an approach designed for refreshable data.

Windows, Mac, and web differences

The VBA procedures above are intended for desktop Excel for Microsoft 365 on Windows. Excel for Mac also supports VBA, although editor shortcuts and some interface details vary by keyboard and release. Use Developer > Visual Basic and Developer > Macros if the Windows shortcut is unavailable. Macro security settings are managed through Excel’s preferences or organizational controls, and should not be weakened just to run an unknown file.

Excel for the web does not run VBA. If a workflow must run in a browser, consider Office Scripts, where available, or Power Query for repeatable data import and transformation. These tools are not VBA equivalents in every respect: Office Scripts use a different programming model, while Power Query is intended for retrieving and reshaping data rather than automating arbitrary worksheet actions.

Frequently Asked Questions

How do I copy data from one sheet to another in Excel VBA?
Set references to the source and destination worksheets, identify the source range, then assign its values to an equally sized destination range or use Range.Copy Destination:=. Choose values assignment for results only and Copy for formulas and formatting.

How do I copy only values, not formulas?
Assign the source range’s .Value to a destination range resized to the same row and column counts. The values-only macro above does this, so formula results become fixed values in the destination.

Does the macro overwrite data in the destination sheet?
It overwrites cells in the area where it writes, but the examples do not clear the entire destination worksheet. Review the target area first; avoid adding a broad clear command unless you are certain every affected cell can be erased.

Can I run a VBA copy macro in Excel for the web?
No. Excel for the web can store and open macro-enabled workbooks, but VBA runs only in the desktop Excel application. Office Scripts may suit some browser-based automation needs.

Why does Ctrl+Z not undo a macro?
Running VBA generally clears Excel’s normal undo history. Save a separate workbook copy before running code, and inspect the source and destination ranges before relying on the result.

Related

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.