How to combine multiple workbooks to one workbook in Excel?

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, clickInsert > Module, and input the following code into the Module:

Sub GetSheets()
Path = "C:\Users\dt\Desktop\dt kte\"
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

Tip: In the above code, you can change the path to the one that you are using.

4. Then click  button to run the code, and all of the worksheets (including the blank worksheets) within the workbooks have been merged into the master workbook.

From: http://www.extendoffice.com/documents/excel/456-combine-multiple-workbooks.html