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?


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.

  • Thanks, keyboard-only definitely makes it easier - and safer. It is possible to record those steps in a macro and run it 30 times? – mtone Jan 25 '12 at 1:24
  • For the most part. If Excel thinks the data tables are actual table objects then Excel will record a couple of the steps with the name of the table and you will have to adjust. The other thing to remember is to make sure to use relative references while recording. Otherwise step 7 will record as selecting the specific row you are moving to. – mischab1 Jan 25 '12 at 2:11

I add the same problem, found an awesome macro here: http://excel.tips.net/T003005_Condensing_Multiple_Worksheets_Into_One.html

Sub Combine()
    Dim J As Integer

    On Error Resume Next
    Worksheets.Add ' add a sheet in first place
    Sheets(1).Name = "Combined"

    ' copy headings
    Selection.Copy Destination:=Sheets(1).Range("A1")

    ' work through sheets
    For J = 2 To Sheets.Count ' from sheet 2 to last sheet
        Sheets(J).Activate ' make the sheet active
        Selection.CurrentRegion.Select ' select all cells in this sheets

        ' select all lines except title
        Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select

        ' copy cells selected in the new sheet on last line
        Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)
End Sub

Just copy-paste the macro, run it and it's done!

  • Is there a way to modify this so that it will continue to update the Combined tab if the other tabs are updated? – MaskedPlant Sep 20 '18 at 20:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.