Excel Macro to Pull Data From Other Worksheets Safely

Yes. In Excel for Microsoft 365 on Windows, a VBA macro can read ranges from other worksheets and copy their values into a destination sheet. For example, the macro below combines rows from every worksheet except one named Summary; it assumes each source sheet has headers in row 1 and data in columns A:D from row 2 onward.

What pulling worksheet data means

“Pull data” can describe several different tasks. A macro might collect the same columns from many monthly tabs, copy a particular cell from one sheet, or find a matching record on another sheet. The best approach depends on whether the source is fixed or changes, whether you need one result or a consolidated table, and whether the destination should contain values, formulas, or formatting.

The example here handles a common case: append the records from several worksheets into one summary table. It uses Excel’s VBA object model to refer to worksheets, inspect the final used row in a key column, and transfer a rectangular range. It copies values rather than using the clipboard, so it does not replace the user’s clipboard contents or depend on selecting and activating sheets.

This approach works well when the workbook’s layout is consistent. It is not a general-purpose data-cleaning system: if one sheet has different columns, extra title rows, or a different meaning for a field, the macro needs additional rules before combining the data.

Check the workbook layout first

Before writing or running a macro, confirm that the source sheets have the same structure. In this example, each source worksheet uses row 1 for headings, and records occupy columns A:D. Column A should have a value on every data row because the code uses it to find the last row. The destination sheet is named Summary and already has the four headings in A1:D1.

Use a clear, consistent tab name for the destination, including the exact spelling used in the code.
Make sure the data columns mean the same thing on every source tab and appear in the same order.
Decide whether hidden worksheets should be included. The example loops through all worksheets except Summary, including hidden ones.
Keep a backup before testing. The macro clears old output values in Summary before building the new result.

If your workbook has unrelated worksheets, do not run this version unchanged: it treats every non-Summary worksheet as a source. You can instead add a name check so only approved tabs are included.

VBA macro to combine rows from worksheets

Save a copy of the workbook before running this macro. It clears existing values in Summary columns A:D below the headings, then writes the current source data there. The code leaves the headings in row 1 intact and does not copy formatting.

Option Explicit

Sub CombineWorksheetData()
    Dim wb As Workbook
    Dim sourceWs As Worksheet
    Dim summaryWs As Worksheet
    Dim lastRow As Long
    Dim lastSummaryRow As Long
    Dim nextRow As Long

    ' Use the workbook that contains this macro.
    Set wb = ThisWorkbook
    Set summaryWs = wb.Worksheets("Summary")

    ' Remove the previous output but keep the heading row.
    lastSummaryRow = summaryWs.Cells(summaryWs.Rows.Count, "A").End(xlUp).Row
    If lastSummaryRow >= 2 Then
        summaryWs.Range("A2:D" & lastSummaryRow).ClearContents
    End If

    nextRow = 2

    ' Copy rows from every worksheet other than the destination.
    For Each sourceWs In wb.Worksheets
        If sourceWs.Name <> summaryWs.Name Then
            lastRow = sourceWs.Cells(sourceWs.Rows.Count, "A").End(xlUp).Row

            If lastRow >= 2 Then
                summaryWs.Cells(nextRow, 1).Resize(lastRow - 1, 4).Value = _
                    sourceWs.Range("A2:D" & lastRow).Value
                nextRow = nextRow + lastRow - 1
            End If
        End If
    Next sourceWs
End Sub

In this HTML display, the code’s less-than and ampersand characters are shown as HTML entities; when entering VBA in the editor, use the ordinary characters < and & rather than the entity spellings. The key transfer statement assigns the source range’s values to a destination range of the same dimensions. A sheet with five data rows contributes five rows, and the next source begins immediately after them. If the source sheet has only its heading row, it contributes no records.

ThisWorkbook means the workbook containing the VBA code. That is usually safer than ActiveWorkbook, which refers to whichever workbook happens to be active when the macro runs. The macro expects a worksheet named Summary; if that tab does not exist, VBA will stop with an error instead of creating it automatically.

How to add and run the macro

Save a working copy as an Excel Macro-Enabled Workbook (.xlsm) using File > Save As. A standard .xlsx file cannot store VBA code.
If the Developer tab is visible, choose Developer > Visual Basic. On Windows, Alt+F11 also opens the Visual Basic Editor. On a Mac, Option+F11 usually opens it; some keyboards require Fn+Option+F11.
In the editor, choose Insert > Module, paste the code into the module, and return to Excel.
Run it from Developer > Macros, select CombineWorksheetData, and choose Run. You can also run a macro from the Visual Basic Editor with the cursor inside the procedure.

If Developer is not visible in Windows Excel, enable it through File > Options > Customize Ribbon and select Developer. Excel for Mac has its own ribbon customization options; the exact dialog differs, but the Developer tab can be enabled in Excel settings. Excel for the web can open a macro-enabled workbook, but it cannot create, edit, or run VBA macros. Use the desktop app for this procedure.

Adapt the macro to your sheets

For a different destination tab, change the text Summary in the worksheet assignment and in the exclusion logic is not necessary: the exclusion compares against the worksheet object’s name. For different source columns, update both the range being copied and the number of columns in Resize. Those dimensions must agree. For example, if your source data is in A:F, use a six-column destination range and copy A2:F followed by the detected last row.

If only named source tabs should be included, test for those names rather than accepting every worksheet. For example, an organization might use January, February, and March as sources but also have Notes and Instructions tabs. Add a condition that permits only the intended source names, or maintain a clearly marked list of eligible tabs. This reduces the risk of mixing notes or unrelated tables into the result.

To pull one known cell rather than combine a table, a small procedure can assign the value from a specific sheet and cell to a destination cell. Declare worksheet variables and set them to the source and destination sheets, then assign the source cell’s Value to the destination cell’s Value. This is appropriate for fixed references such as a report total; it is not a substitute for a lookup when the row containing the desired record can move.

The sample transfers values only. If source cells contain formulas, the destination receives their calculated results, not the formulas themselves. It also does not copy number formats, colors, borders, comments, or column widths. Formatting can be applied separately on Summary, which often produces a more consistent report than copying formatting from tabs that may have drifted apart.

Common errors and how to prevent them

Subscript out of range: Excel cannot find a worksheet with the exact name Summary in ThisWorkbook. Check spelling, spaces, and whether the macro is stored in the workbook you intend to process.
Some records are missing: the macro finds the final row using column A. If that column has blanks while other columns still contain data, the detected range may end too soon. Use a column that is filled for every record, or change the last-row detection logic to match your layout.
Unexpected tabs appear in the output: all worksheets except Summary are treated as sources. Add an inclusion rule for approved tabs, and remember that hidden worksheets are still in the Worksheets collection.
Old output remains: the clearing step finds the previous output’s last row using column A. If old records have blanks in column A, some values farther down could remain. Clean the destination deliberately or revise the clearing logic to identify the actual output area.
Columns do not line up: every source range is copied positionally. The macro does not compare heading names or rearrange fields, so inconsistent column order can silently put values under the wrong headings.

For a reusable production macro, add validation before clearing the summary: confirm that the destination exists, inspect source headings, and stop with a clear message if a sheet does not match the expected layout. Testing on a duplicate workbook with a small set of sample tabs helps reveal layout assumptions before a larger run.

Macro, formula, or Power Query?

A macro is useful when you want a repeatable action, need to apply custom VBA logic, or want users to run a button-driven process. It is flexible, but it requires a macro-enabled file, desktop Excel, and appropriate macro security settings. VBA macros cannot be undone with Ctrl+Z, so a backup is important before a procedure that clears or replaces data.

A worksheet formula may be simpler when you need a live reference to one known cell or a calculation that should update as its source changes. Formulas are visible in the grid and recalculate according to workbook settings, but manually combining many changing tabs with formulas can become difficult to maintain.

Power Query is often a good option for repeatable imports and consolidation when the source data is tabular and the main need is to transform or append it. It can be refreshed without running a VBA procedure and is available in desktop Excel for Microsoft 365, though available connectors and interface details can vary by platform. Choose based on how the data changes, who must refresh it, and whether the task needs custom VBA behavior.

Safety and workbook behavior

Macros can change workbook contents, so only enable code from sources you trust. Excel may block macros from files downloaded from the internet or apply organization-managed security policies. Do not solve a blocked-macro warning by lowering security for every workbook; follow your organization’s approved process or use a trusted, verified file location where permitted.

Remember that the sample clears cells in Summary before copying new data, and the action is not reversible with Ctrl+Z. Save a separate copy, verify the source tabs and destination headings, and inspect the resulting row count and sample records after running. If other formulas or charts depend on Summary, check that replacing its old data will not disrupt the report.

Frequently Asked Questions

Can an Excel macro pull data from multiple worksheets at once?
Yes. A VBA procedure can loop through the workbook’s worksheets and copy a matching range from each one into a single destination. The sheets need a consistent layout, or the procedure must include rules to handle their differences.

Does this macro copy formulas or their results?
The sample assigns the source range’s Value property, so it copies the calculated values shown by formulas rather than the formulas themselves. To preserve formulas, the transfer needs to use a formula property and the destination formulas must make sense in their new location.

Can I run a VBA macro in Excel for the web?
No. Excel for the web does not run VBA macros. Open the workbook in the Excel desktop application on Windows or Mac to use this VBA procedure.

Will the macro include hidden worksheets?
Yes. It loops through the Worksheets collection and excludes only the Summary sheet. Add an explicit visibility or sheet-name condition if hidden tabs should be skipped.

Can I undo a macro with Ctrl+Z?
No. Running VBA clears Excel’s normal undo history, and macro changes cannot be reversed with Ctrl+Z. Save a backup before running code that clears, replaces, or reorganizes worksheet 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.