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>> is just one of 120 powerful features of Kutools.

Kutools for Excel: add 120 new features in Excel. Save one hour every day.
Classic Menu for Office: brings back classic menus to Office 2010 and 2013 (includes Office 365).
Office Tab: brings tabbed interface to Office as the use of web browser Chrome, Firefox and Internet Explorer.

arrow blue right bubble Combine multiple workbooks to one workbook with VBA

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

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:

doc-merge-multiple-workbooks1

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 doc-merge-multiple-workbooks-button 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.


arrow blue right bubble 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.

doc-merge-multiple-workbooks2

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:

doc-merge-multiple-workbooks3doc-merge-multiple-workbooks-arrow2doc-merge-multiple-workbooks4

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:

doc-merge-multiple-workbooks5
doc-merge-multiple-workbooks-arrow1
doc-merge-multiple-workbooks6

arrow blue right bubble 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 Combine 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 > Combine.

doc-merge-multiple-workbooks7

1. In the Combine Worksheets wizard, select Combine multiple worksheets from workbooks into one workbook. See screenshot:

doc-merge-multiple-workbooks8

2. On step2 of wizard, all the opened workbooks and worksheets are listed in the list boxes, you can click Add button to add other files or folders that you want to combine. Then check the specific worksheets you want to merge from the Worksheets list. See screenshot:

doc-merge-multiple-workbooks9

3. After specifying the worksheets that you want to merge, then continue to click the Next button, on step3 of wizard, you can do the following options:

doc-merge-multiple-workbooks10

Tips

  • (1.) You can rename the new combined worksheets by inserting the workbook names before or after the original worksheets, or you can just keep the original worksheet names by not check By inserting workbook name option.
  • (2.) If your workbooks contain some blank worksheets, you can keep or skip the blank worksheets as you need by clicking Skip drop down list.

4. Then click Finish button to merge the workbooks, and you will be asked if you want to save this scenario. If you want to save this scenario, please click Yes, and enter the name of the scenario, (see following screenshots), if you don’t want to save this scenario, please click No. When you want to use this scenario, you just only need to click Scenario button to choose the name of the scenario you need on step2 wizard.

doc-merge-multiple-workbooks11doc-merge-multiple-workbooks-arrow2doc-merge-multiple-workbooks12

5. Then the specified worksheets have been merged into a new workbook, and the worksheets are named with the workbook name before original worksheet name. A new worksheet named Kutools is also generated at the front of all the worksheets at the same time, which lists the workbooks with detailed information of each worksheets. See screenshot:

doc-merge-multiple-workbooks13

Note: If your worksheet name is too long, maybe the name characters are greater than 31characters, the combined name will not be displayed, and only show the original sheet name.

If you want to know more about this feature, please click Combine multiple worksheets from workbooks into one workbook.


arrow blue right bubble Related Articles:


Kutools for Excel

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

screen shot

btn read more     btn download     btn purchase

Comments  

-1#A. Karthi2013-10-23 05:37
Hi Plz help me out to this below condition.

I have different workbook which has more than 5 worksheets each in different path. I need to consolidate all worksheets from different workbook into single workbook. Can anyone help me to resolve with macro.TIA!
Reply | Reply with quote | Quote
+1#Jay Chivo2013-10-24 06:04
Quoting A. Karthi:
Hi Plz help me out to this below condition.

I have different workbook which has more than 5 worksheets each in different path. I need to consolidate all worksheets from different workbook into single workbook. Can anyone help me to resolve with macro.TIA!


Please go to download and install the Kutools for Excel, you can quickly get it done. But if you want to use a VBA, it may be too complicated.

For more information about how to get it done, please visit:http://www.extendoffice.com/product/kutools-for-excel/excel-combine-worksheets-into-one.html
Reply | Reply with quote | Quote
+3#Susie2013-10-31 15:49
I get a 'runtime error 1004', Copy Method of Worksheet class failed on the line that reads: Sheet.Copy After:=ThisWorkbook.Sheets(1). I'm using Excel 2010.

Can you assist? Thanks, - Susie
Reply | Reply with quote | Quote
+2#Amir2013-11-12 11:07
Hi!
Thanks a lot of this file.............
:roll:
Best Regard
Reply | Reply with quote | Quote
+1#Henrik2013-12-06 11:32
Hey

Great tip. Did almost all I wanted.

In the combinde woorkbook, I would have lovede for the worksheet name to contain the name of the original woorkbook, so I know which woorkbook the data comes from.

The data I'm combining, is from different archives. I have to search for an entry, but don't know which archive it's in.

So by combining all the data in one file will make it possible for me to search all archives at once.

But I still need to know, which archive the entry is in.

Henrik
Reply | Reply with quote | Quote
0#Tedi2013-12-09 09:18
This is superb :lol: helped me a lot....
Reply | Reply with quote | Quote
0#Amol2013-12-12 09:06
Thanks mate, u made my day from this very helpful website... Actually i was also wanted to combine the same header data of different sheets into 1 master worksheet, the KUTOOL for Excel helped me a lot.... Thank you once again.... :)
Reply | Reply with quote | Quote
-1#Dinesh2013-12-12 09:58
Thanks much for the valuable information. This really works. The steps listed in this article really made my job easier.

Thanks,
Dinesh
Reply | Reply with quote | Quote
0#hamid2013-12-29 20:40
thanks for sharing your knowledge
Reply | Reply with quote | Quote
-2#Ness2014-01-14 01:18
How do you get it to update the changes from the original workbook? I'm trying to get a national summary which will have each region input their data into their own workbooks and then have the national summary which updates from this? I'd like to have this set up for the full year at the beginning and not be working retrospectively.
Reply | Reply with quote | Quote
0#Dado2014-01-16 03:50
After combining the worksheets in one Workbook ,how do you save it I couldn't save it is named Book1 and I am clicking on save or save as but not working.any suggestions?
Reply | Reply with quote | Quote
0#Sherrill2014-01-17 23:05
I followed the steps in "Combine multiple workbooks to one workbook with VBA" and click on "run", nothing happend. I am not aware of errors and not sure how to correct. Would you help me? the following is the code I input in a new workbook.

Thank you

Sub GetSheets()
Path = "p:\download\macro\"
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
Reply | Reply with quote | Quote
-1#Sherrill2014-01-17 23:09
Dear Sir/Madam: I followed the steps of "Combine multiple workbooks to one workbook with VBA" to set up the following module, but nothing happened. Would you help me find the problem? thank you



Sub GetSheets()
Path = "p:\download\macro\"
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
Reply | Reply with quote | Quote
-3#Dave2014-01-27 22:25
for newer versions of excel, try this. I saved my open workbook as catalog, and all the files are in c:\temp.

Sub GetSheets()
Path = "c:\temp\"
Filename = Dir(Path & "*.xls")
Do While Filename ""
Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
For Each Sheet In ActiveWorkbook.Sheets
Sheet.Copy After:=Workbooks("catalog.xlsx").Sheets(1)
Next Sheet
Workbooks(Filename).Close
Filename = Dir()
Loop
End Sub
Reply | Reply with quote | Quote
-3#Dave2014-01-27 22:26
Try this for newer versions of excel. I saved my workbook as catalog, all the files are in c:\temp.

Sub GetSheets()
Path = "c:\temp\"
Filename = Dir(Path & "*.xls")
Do While Filename ""
Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
For Each Sheet In ActiveWorkbook.Sheets
Sheet.Copy After:=Workbooks("catalog.xlsx").Sheets(1)
Next Sheet
Workbooks(Filename).Close
Filename = Dir()
Loop
End Sub
Reply | Reply with quote | Quote
+1#jlhall072014-03-21 16:13
Quoting Dave:
Try this for newer versions of excel. I saved my workbook as catalog, all the files are in c:\temp.

Sub GetSheets()
Path = "c:\temp\"
Filename = Dir(Path & "*.xls")
Do While Filename ""
Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
For Each Sheet In ActiveWorkbook.Sheets
Sheet.Copy After:=Workbooks("catalog.xlsx").Sheets(1)
Next Sheet
Workbooks(Filename).Close
Filename = Dir()
Loop
End Sub


I keep getting "can't assign to read-only property" concerning the path... Any idea?
Reply | Reply with quote | Quote
0#sara2014-03-27 21:07
Im having tis problem too. Did you figure it out?
Reply | Reply with quote | Quote
0#jlhall072014-03-28 12:56
Nothing yet... Haven't found any solution or had anyone suggest a fix. Sorry...
Reply | Reply with quote | Quote
0#Patrick2014-04-11 18:13
I was looking for something along these lines but wanted to comment. Doesn't the Do While Filename "" need to be something other than ""? or am I reading that wrong?

Maybe Do While NOT filename = ""

Just a thought...
Reply | Reply with quote | Quote
0#Anand Darbha2014-01-28 12:45
I want to combine all the sheets into one sheet where the headings are common...pls help
Reply | Reply with quote | Quote
-2#Sanora2014-02-27 12:13
Once the movie was shot, I would put the scene into play.
Later on you can then move towards parts that you want to service that you choose aren't also far
away from dwelling. With such a high quantity and diverse range of
companies offering employment in this Sector such as Hospitality, Retail, Public Health, Media, and Financial Services, students at colleges in London will have direct access to an assortment of job vacancies.


Review my website: escorts london} (Sanora)
Reply | Reply with quote | Quote

Add comment


Security code
Refresh