How to combine multiple workbooks to one workbook in Excel?

Have you ever been stuck when you have to combine multiple workbooks into a single workbook in Excel? The most terrible thing is that the workbook you need to combine contains multiple worksheets. Can anyone suggest a good way to handle this problem?

Combine multiple workbooks to one workbook with VBA
For advanced users, complicated.

Combine multiple workbooks to one workbook with Move or Copy command
Time-consuming operations.

Combine multiple workbooks to one workbook with Kutools for Excel
Easy, simple.

Combine multiple workbooks to one workbook with VBA

Amazing! Using Tabs in Excel like Firefox, Chrome, Internet Explore 10!

Learn more Free download

For the skilled and professional programmers, you can use VBA scripts to combine multiple workbooks into one master workbook. You can deal with this with the following steps:

1. Put all the workbooks that you want to combine into the same directory. See screenshot:

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 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.

Note: This VBA code can merge the entire workbooks into the master workbook, if you want to combine specified worksheets of the workbooks, this code will not work.

Combine multiple workbooks to one workbook with Move or Copy command

If you are a rookie of Microsoft Excel, you have no choice but only have to copy the data of every sheet and paste them in to a new workbook one by one and applying the Move or Copy command. Using the Move or Copy command will help you export or copy one or several worksheets to a new workbook quickly.

1. Open all workbooks that you want to merge into a single workbook.

2. Select all of the worksheet names of a workbook in tab bar. You can select multiple with holding down Ctrl key or Shift key. Right click the worksheet name, and click the Move or Copy from context menu.

3. In Move or Copy dialog box, select the master workbook that you want to merge other workbooks into from the drop down list of Move selected sheets to book. And then specify the location of the merged worksheets. See screenshots:

4. Then click OK. The selected worksheets have been moved to the master workbook.

5. Repeat the steps from 2 to 4 to move other workbooks to the master workbook. Then it combines all worksheets of opened workbooks into a single workbook. See screenshots:

Combine multiple workbooks to one workbook with Kutools for Excel

Fortunately, for rookies and new hands of Microsoft Excel, there are some available tools to help you quickly combine hundreds of workbooks into one. The function Merge Workbooks in Kutools for Excel makes it much easier to combine multiple Excel files. The application is used for releasing the repetitive and redundant operations in Excel. It actually plays an important role if you have to deal with a large amount of Excel file in your daily work. Now, let's see how to get this function work in combining multiple workbooks.

Kutools for Excel: with more than 120 handy Excel add-ins, free to try with no limitation in 30 days. Get it Now.

After installing Kutools for Excel, please click Enterprise > Merge Workbooks.

1. In the Merge Workbooks dialog box, click Add file to add the workbooks one by one or click Add folder to add multiple workbooks staying in a folder that you want to merge.

2. Then the worksheets of the specified workbooks will be imported into the list box. You can use the Up or Down arrow button to rearrange the worksheets order. By default, all of the worksheets are selected, you can use Unselect All button to quickly uncheck all of them. Then select the specific worksheets you want from the list to combine. See screenshot:

3. And then click Merge, all of the worksheets you selected have been merged into a single workbook.

Notes:

1. If the worksheets that you merged contain blank sheets, you can skip them or keep them when you combine the worksheets.

2. If the worksheets that you merged contain hidden sheets, you can skip them. Also you can keep them and display or continuing hidden them after merging.

If you want to know more about this Merge Workbooks feature, please click here.

Related Articles:

• Split a workbook to separate Excel files
• Insert worksheets from another workbook
• Export and save sheets and worksheets as new workbook

Kutools for Excel

More than 120 Advanced Functions for Excel 2013, 2010, 2007 and Office 365.