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.
Ctrl + Home to jump to the top left of the spreadsheet.
(This will jump to A1 unless there are rows or columns frozen.)
Shift + Ctrl + End to select everything left and down of the current selected cell.
Ctrl + X to cut the data.
Ctrl + Tab to switch to the new workbook.
- Double-check you are in the correct location and press
Enter to paste the data.
Ctrl + End to go to the last cell pasted.
Down Arrow to go to the next unused row.
Ctrl + Left Arrow to go back to column A.
Ctrl + Tab to switch back to the original workbook.
Ctrl + Page Down to select the next worksheet.
- 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.