Aggregated Summary Table VBA Macro for Excel - Merge Data from Multiple Worksheets

09 Jan 2019

The Problem

My girlfriend wanted to combine three tables in Excel. They all existed in the same workbook. There are three different tabs with data, all with the same headers.

The Failed Attempts

Somewhat unbelievably, it is very difficult to join these three tables in Excel and perform operations on the data. There are many ways to unsuccessfully skin this cat, including:

1. Long IFERROR/INDEX formulas that bring data over from other tabs
   • Doesn’t allow for filtering or sorting, very breakable, not dynamically scalable to multiple sheets
2. VLookups
   • Not very dynamic, doesn’t work for this purpose well
3. Pivot Tables
   • Pivot tables don’t offer the ability to display raw data - only aggregate numbers.

The Working Solution

I ended up pulling together a VBA script to copy and paste the data from each sheet into the Master sheet. It is very simple. I cannot believe Excel doesn’t offer this functionality built-in.

There are a few steps to the script:

1. It creates a sheet named “Master”, if one doesn’t already exist.
2. The script clears the existing text from “Master” in preparation for the next step.
3. It copies the content of each sheet in the workbook to “Master” - starting each sheet on the second row.
   • Starting on the second row allows you to use headers without copying them over.
   • Sheets are copied in the order that they appear in the workbook.
4. Done! Enjoy your merged tables.

If you don’t care about code and just want a sample workbook, here’s your chance.

The Code

Below, I’ve embedded the full contents of the macro that can power this feature. You can drop this macro into your Excel workbook and begin merging tables immediately.

The sample workbook has a button provided hooked up to ThisWorkBook.RunMe. The RunMe subroutine will create a “Master” sheet (if it doesn’t already exist), and then copy data from every other sheet in the workbook over.

If you want to extend this script, I have commented it pretty thoroughly.

If you want to change the name of the generated sheet, change the masterSheetName in the RunMe subroutine.

If you add more columns to your tables, make sure to adjust the subroutine ClearMasterSheet - it currently clears A2:H9999. Adjust to your needs.