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.
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:
INDEXformulas that bring data over from other tabs
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:
If you don’t care about code and just want a sample workbook, here’s your chance.
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
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
If you add more columns to your tables, make sure to adjust the subroutine
ClearMasterSheet - it currently clears
A2:H9999. Adjust to your needs.