Combine tables in several sheets to one sheet

Question

I have an excel document with over 30 sheets. Each has the same layout: a simple table having the same columns, header/total rows, but different number of rows (none has more than 500 rows).

I want to combine them into a master table. One time operation only.

Manually, I could copy 30 table one after the other on a new worksheet. When done, I'd do a sort and delete any blank/header/total row. I don't care about the formatting. I guess it would take me about 15-20 minutes, but there always the risk I do a mistake and miss a sheet or something.

VBA sounds like it would take too much time. Right now I'm almost considering importing the workbook into SQL Server, UNION them and copy-paste the result back to excel.

Do you have any good tricks?

Answer 1

If this is something you will need to do frequently then I recommend writing a quick VBA routine. If it is a one-time thing then I would just do it manually. To make sure you don't accidentally skip a table, use cut-paste instead of copy-paste.

Here is how I would do this. It goes fairly quickly with keyboard shortcuts.

• Open the original workbook in Excel as read-only to prevent any accidental loss of data.
• Open a new workbook to hold the new Master Table.
• Close any other workbooks so it is easy to switch between the two you are working with.
• Select the first worksheet in the original workbook that you want to copy.

Now you can start using keyboard shortcuts.

1. Ctrl + Home to jump to the top left of the spreadsheet. (This will jump to A1 unless there are rows or columns frozen.)
2. Shift + Ctrl + End to select everything left and down of the current selected cell.
3. Ctrl + X to cut the data.
4. Ctrl + Tab to switch to the new workbook.
5. Double-check you are in the correct location and press Enter to paste the data.
6. Ctrl + End to go to the last cell pasted.
7. Down Arrow to go to the next unused row.
8. Ctrl + Left Arrow to go back to column A.
9. Ctrl + Tab to switch back to the original workbook.
10. Ctrl + Page Down to select the next worksheet.
11. Go back to step 1 and repeat until done.

When you are done, you can Ctrl + Page Up back thru all the original worksheets and verify all the data has been removed.

Assuming your title row isn't frozen this will copy the title row from all 30 tables. In that case either sort the master table to group all the duplicate title rows and delete the extras or use Data -> Remove Duplicates.

If you accidentally skip a step or paste over existing data, press Ctrl + Z to undo.

I just did a test following the above steps. The workbook I used had 10 worksheets each with 120 rows of data. It took me 1 ½ minutes to create the master table. If you aren't familiar with keyboard shortcuts it might take you twice as long. For 30 worksheets I would guess it might take you 10 minutes.