How to Combine Multiple Workbooks to Single Workbook in Microsoft Excel?

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

Actually there are three ways you can go through with the issue. For the skilled and professional programmers, you can use Macro VBA scripts to combine the CSV, XLS Excel files. The codes below are excerpted from the MrExcel* forum , where you can click the link to get the complete code.

“'Description: Combines all files in a folder to a master file.
Sub MergeFiles()
Dim path As String, ThisWB As String, lngFilecounter As Long
Dim wbDest As Workbook, shtDest As Worksheet, ws As Worksheet
Dim Filename As String, Wkb As Workbook
Dim CopyRng As Range, Dest As Range …”

*A website provides tips and books of Microsoft Excel, and is designed for advanced Excel users.

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. This “copy, paste, copy…” process is so tedious and time-consuming. Let me share you my experience. I had spent 9 hours to combine 10 workbooks with nearly 200 worksheets. When the work is finished, God, I was worn out and never wanted to click the Excel icon again!

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

After installing Kutools for Excel, you will see a Kutools tab added in the ribbon, and just click it to find out the groups of functions there. Move to Worksheet Manager, Choose the Merge Workbook… See screenshot (Fig.1).

combine-files
Fig.1 Merge Workbook in Kutools for Excel

With Merge Workbook in Kutools for Excel, you are able to add the workbooks one by one by clicking the Add File button, or clicking the Add Folder button to add multiple workbooks staying in a folder. After adding the workbooks, the worksheets of multiple workbooks will be listed under the worksheet name, and all the worksheets have been selected. You can use Unselect All button to quickly uncheck all of them. Then select the specific worksheet you want from the list to combine.

combine-files-new
Fig.2 Merge multiple workbooks pop-up window

Sometimes there will be empty sheets in the workbook, while is hard to pick them up among hundreds of worksheets. Using the Merge Workbooks function, you can keep or skip the blank sheets in the process of combining. Meanwhile, the hidden worksheets can also be skipped. If you choose to keep the hidden worksheets, then the status of the sheets can be kept as either unhidden or hidden. See screenshot (Fig.3)

keep-hidden-sheet
Fig.3 Keep hidden worksheets in the process of combining multiple workbooks