Combine All Sheets Into One

What it does

Stacks the data from every worksheet in your workbook into one new sheet called Combined. If your sheets all share the same headers (the common case for monthly or per-branch sheets), it keeps just one header row at the top and skips the duplicates from sheets two, three, four, and on. The source sheets are read-only, so nothing gets damaged. Faster than copy-pasting one tab at a time, lighter than setting up Power Query for a one-off.

Real-world example

A finance manager at a 12-store retail chain runs the monthly P&L review. Each store sends in their numbers as a separate sheet in a shared workbook: Store-Bandra, Store-Indiranagar, Store-Andheri, on through 12 tabs, all with identical column headers (Date | Category | Amount | Notes). To run a single pivot table across all stores, she needs the data stacked into one sheet.

Her current method: open the workbook, click into Store-Bandra, Ctrl+A, Ctrl+C, click into a new sheet, Ctrl+V. Then back to Store-Indiranagar, Ctrl+A but skip the header row, Ctrl+C, paste below the previous block. Repeat for ten more stores. Forty minutes in, she realizes she pasted Store-Bandra's header row twice and has to find and delete the orphan. By the time the master sheet is clean, an hour has gone by, and she still has to build the pivot.

With this macro, she clicks into the workbook, hits Alt + F8, runs CombineAllSheets, clicks Yes when asked about headers, and 4 seconds later there's a Combined sheet at the front with one header row and 12 stores' worth of data stacked underneath. She builds the pivot. The whole consolidation is done before her coffee gets cold.

Realistic time saved per monthly close: 45 to 55 minutes. Per year: about 10 hours. The bigger win is the source sheets stay clean (no copy-paste artifacts) and the consolidation is repeatable, which matters when the regional manager asks for a re-run with one store's data corrected.

How to use

First time? See How to run our tools for the 30-second setup.

1. Open the workbook with the sheets you want to combine, alongside the macro workbook.
2. Click into the data workbook so it's the active one.
3. Press Alt + F8 / Option + F8, pick CombineAllSheets, click Run.
4. When asked "Does the first row of each sheet contain headers?", click Yes if your sheets share headers (most common). Click No for header-less data.
5. A new Combined sheet appears at the front of your workbook.

FAQ

What if my sheets have different headers?

The macro stacks them in tab order, so the column positions of sheet 1 determine the layout. Sheets with mismatched columns will end up with their data in the wrong columns. Either standardize the headers across all sheets first, or use Power Query's Append Queries for non-uniform sources.

Will it overwrite an existing Combined sheet?

Yes. If a sheet called "Combined" already exists, the macro deletes it first and creates a fresh one. Save your previous result under a different name if you want to keep it.

Does it preserve formatting and formulas?

Cell formatting (fonts, colors, borders) is preserved. Formulas are copied as-is, but formulas that reference other sheets may break after the move. If that matters, paste-as-values in the source sheets first.

How big a workbook can it handle?

Excel's row limit is 1,048,576. As long as your stacked total fits, the macro works. Performance is good up to a few hundred thousand rows.

Does it skip hidden sheets?

No, hidden sheets are processed too. If you want to skip them, unhide first to verify what's included, then re-hide before running.