The objective for this post is to learn how to combine (merge) more than one Excel worksheet file into another Excel file, and then combine the group of worksheets into one worksheet. Each original Excel file (aka workbook) contains one worksheet that has data with the same headers on the top row, and no other data. Also, the data must start at cell A1 and have the same structure. You need to bring it all together into one Excel file with one worksheet. How you you do that?

This is a two-step process. First you need to create a new empty Excel file that will contain all of the worksheets of all of the other Excel files. All of the other Excel files need to be in a folder with nothing else in the folder other than the Excel sheet you need to combine. Next you need to take that new Excel file and combine the worksheets into one worksheet. You do this with two sets of VBA Code. You can open the VBA editor in Excel with Atl+F11.

Phase 1 – Combine Workbooks (Merge Files)

In the code below, replace C:\MyExcelWorksheets\ with the location of your Excel files. The following code is from the ExtendOffice website.

Sub GetSheets()
   Path = "C:\MyExcelWorksheets\"
   Filename = Dir(Path & "*.xls")
   Do While Filename <> ""
      Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
      For Each Sheet In ActiveWorkbook.Sheets
         Sheet.Copy After:=ThisWorkbook.Sheets(1)
      Next Sheet
   Workbooks(Filename).Close
   Filename = Dir()
   Loop
End Sub

Phase 2 – Merge Worksheets

The following code is from ExtendOffice Your data must start at A1 otherwise it won’t work..

Sub Combine()
   Dim J As Integer
   On Error Resume Next
   Sheets(1).Select
   Worksheets.Add
   Sheets(1).Name = "Combined"
   Sheets(2).Activate
   Range("A1").EntireRow.Select
   Selection.Copy Destination:=Sheets(1).Range("A1")
   For J = 2 To Sheets.Count
      Sheets(J).Activate
      Range("A1").Select
      Selection.CurrentRegion.Select
      Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select
      Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)
   Next
End Sub

Phase 1 Steps

1. Put all the workbooks that you want to combine into the same directory.
2. Launch an Excel file that you want to combine other workbooks into.
3. Click Developer > Visual Basic, a new Microsoft Visual Basic for applications window will be displayed, click Insert > Module, and input the above first set of code into the Module.
4. Change the path to the one that you are using.
5. Click run button to run the code, and all of the worksheets (including the blank worksheets) within the workbooks have been merged into the master workbook.

Phase 2 Steps

1. Hold down the ALT + F11 keys, and it opens the Microsoft Visual Basic for Applications window.
2. Click Insert > Module, and paste the above phase two code in the Module Window.
3. Then press F5 key to run the code, and all the data in the workbook has been merged into a new worksheet named Combined which will add before all worksheets.