Have you ever been stuck when you have to combine multiple workbooks into a master workbook in Excel? The most terrible thing is that the workbooks you need to combine contain multiple worksheets. And how to combine only the specified worksheets of multiple workbooks into one workbook? This tutorial demonstrates several useful methods to help you solve the problem steps by steps. Show
Combine multiple workbooks Into one workbook with Move or Copy functionIf there are just a couple of workbooks need to be combined, you can use the Move or Copy command to manually move or copy worksheets from the original workbook to the master workbook. 1. Open the workbooks which you will merge into a master workbook. 2. Select the worksheets in the original workbook that you will move or copy to the master workbook. Notes: 1). You can select multiple non-adjacent worksheets with holding the Ctrl key and clicking the sheet tabs one by one. 2). For selecting multiple adjacent worksheets, please click on the first sheet tab, hold the Shift key, and then click the last sheet tab to select them all. 3). You can right click on any sheet tab, click on Select All Sheets from the context menu to select all worksheets in the workbook at the same time. 3. After selecting the needed worksheets, right click the sheet tab, and then click Move or Copy from the context menu. See screenshot: 4. Then the Move or Copy dialog pops up, in the To book drop-down, select the master workbook you will move or copy worksheets into. Select move to end in the Before sheet box, check the Create a copy box, and finally click the OK button. Then you can see worksheets in two workbooks combined into one. Please repeat the above steps to move worksheets from other workbooks into the master workbook. Combine multiple workbooks or specified sheets of workbooks to a master workbook with VBAIf there are multiple workbooks need to be merged into one, you can apply the following VBA codes to quickly achieve it. Please do as follows. 1. Put all workbooks that you want to combine into one under the same directory. 2. Launch an Excel file (this workbook will be the master workbook). 3. Press the Alt + F11 keys to open the Microsoft Visual Basic for applications window. In the Microsoft Visual Basic for applications window, click Insert > Module, then copy below VBA code into the Module window. VBA code 1: Merge multiple Excel workbooks into one
Notes: 1. The above VBA code will keep the sheet names of the original workbooks after merging. 2. If you want to distinguish which worksheets in the master workbook came from where after merging, please apply the below VBA code 2. 3. If you just want to combine specified worksheets of the workbooks into a master workbook, the below VBA code 3 can help. In VBA codes, “C:\Users\DT168\Desktop\KTE\” is the folder path. In the VBA code 3, "Sheet1,Sheet3" is the specified worksheets of the workbooks you will combine to a master workbook. You can change them based on your needs. VBA code 2: Merge Workbooks into one (each worksheet will be named with prefix of its original file name):
VBA code 3: Merge specified worksheets of workbooks into a master workbook:
4. Press the F5 key to run the code. Then all worksheets or specified worksheets of the workbooks in the certain folder are combined to a master workbook at once. Easily combine multiple workbooks or specified sheets of workbooks to one workbookFortunately, the Combine workbook utility of Kutools for Excel makes it much easier to merge multiple workbooks into one. Let's see how to get this function work in combining multiple workbooks. Before applying Kutools for Excel, please download and install it firstly. 1. Create a new workbook and click Kutools Plus > Combine. Then a dialog pops to remind you that all combined workbooks should be saved and the feature can't be applied to protected workbooks, please click the OK button. 2. In the Combine Worksheets wizard, select Combine multiple worksheets from workbooks into one workbook option, and then click the Next button. See screenshot: 3. In the Combine Worksheets - Step 2 of 3 dialog box, click the Add > File or Folder to add the Excel files you will merge into one. After adding the Excel files, click the Finish button and choose a folder to save the master workbook. See screenshot: Now all workbooks are merged into one. Compared with the above two methods, Kutools for Excel has the following advantages:
If you want to have a free trial (30-day) of this utility, please click to download it, and then go to apply the operation according above steps. Kutools for Excel - Helps You Always Finish Work Ahead of Time, Have More Time to Enjoy Life Do you often find yourself playing catch-up with work, lack of time to spend for yourself and family? Kutools for Excel can help you to deal with 80% Excel puzzles and improve 80% work efficiency, give you more time to take care of family and enjoy life. 300 advanced tools for 1500 work scenarios, make your job so much easier than ever. Oldest First Sort comments by Oldest First Newest First Comments (146) No ratings yet. Be the first to rate! A. Karthi about 9 years ago This comment was minimized by the moderator on the site 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 0 0 Jay Chivo A. Karthi about 9 years ago This comment was minimized by the moderator on the site [quote]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!By A. Karthi[/quote] 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 0 0 Taslima A. Karthi about 6 years ago This comment was minimized by the moderator on the site KUTOOLS Awsome solution. I need one more help when I create master workbook then cell color of work sheet is changed from original worksheet. How can I keep it like original worksheet. Reply 0 0 E nic A. Karthi about 5 years ago This comment was minimized by the moderator on the site Our office has duplication of data, (i.e.-name, address, city, amount, date signed) from several excel originals and trying to combine the data is going to be a work in progress. How can that be done to eliminate double work and double information entries? Reply 0 0 Susie about 9 years ago This comment was minimized by the moderator on the site 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 0 0 samuel Birch Susie about 6 years ago This comment was minimized by the moderator on the site Hey Susie, Ive been working on this problem for a while now myself, getting the same error. Check to see if the module was created under PERSONAL instead of your active workbook. Once I created the module under the correct tree, the below code worked just fine. Sub GetSheets_xls() Dim Sheet As Worksheet Path = "C:\Users\yournamehere\Desktop\Testingfolder\" Filename = Dir(Path & "*.xls") Do While Filename "" Workbooks.Open Filename:=Path & Filename, ReadOnly:=False Set Sheet = ActiveWorkbook.Sheets(1) Sheet.Copy After:=ThisWorkbook.Sheets(1) 'Next Sheet Workbooks(Filename).Close Filename = Dir() Loop End Sub Hope this helps! Reply 0 0 DS samuel Birch about 5 years ago This comment was minimized by the moderator on the site Thanks a lot. Your code worked well. Reply 0 0 Chris F Susie about 5 years ago This comment was minimized by the moderator on the site [quote]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, - SusieBy Susie[/quote] Had the same problem, it works when I go to view and unhide "PERSONAL", it seems to have trouble accessing this macro with the master hidden. Reply 0 0 Chris F Susie about 5 years ago This comment was minimized by the moderator on the site Go to view and unhide "PERSONAL" - it seems to have trouble executing the whole code while the master is hidden. You can make the macro native to that workbook, but you'd have to recreate the whole thing every time you wanted to use it Reply 0 0 Amir about 9 years ago This comment was minimized by the moderator on the site Hi! Thanks a lot of this file............. :roll: Best Regard Reply 0 0 Henrik about 9 years ago This comment was minimized by the moderator on the site 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 0 0 Code Henrik about 8 years ago This comment was minimized by the moderator on the site For the code to incorporate the file name just do this. Sub GetSheets() Dim temp As String Path = "C:\Users\....\Desktop\Excel combine\" Filename = Dir(Path & "*.xlsx") Do While Filename "" Workbooks.Open Filename:=Path & Filename, ReadOnly:=True temp = ActiveWorkbook.Name ActiveSheet.Name = temp ActiveWorkbook.Sheets(temp).Copy After:=ThisWorkbook.Sheets(1) Workbooks(Filename).Close Filename = Dir() Loop End Sub Note: this is to only copy the first sheet, it can be tweeked to do all sheets Reply 0 0 LAW Code about 8 years ago This comment was minimized by the moderator on the site How do you incorporate more sheets and how do you specify a different master file to paste all the sheets. Reply 0 0 Adriano Marcato Code about 7 years ago This comment was minimized by the moderator on the site It's a great solution indeed. thank you. One Problem though, when I execute it like this excel will ask if I want to save alterations before closing (Since the name was changed), and I don't want to do it for every file (around 32 per execution). Would there be a way to solve this? Reply 0 0 Tedi about 9 years ago This comment was minimized by the moderator on the site This is superb :lol: helped me a lot.... Reply 0 0 Amol about 9 years ago This comment was minimized by the moderator on the site 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 0 0 Dinesh about 9 years ago This comment was minimized by the moderator on the site Thanks much for the valuable information. This really works. The steps listed in this article really made my job easier. Thanks, Dinesh Reply 0 0 hamid about 9 years ago This comment was minimized by the moderator on the site thanks for sharing your knowledge Reply 0 0 Ness about 9 years ago This comment was minimized by the moderator on the site 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 0 0 Dado about 9 years ago This comment was minimized by the moderator on the site 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 0 0 Sherrill about 9 years ago This comment was minimized by the moderator on the site 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 0 0 Sherrill about 9 years ago This comment was minimized by the moderator on the site 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 0 0 Dave about 9 years ago This comment was minimized by the moderator on the site 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 0 0 Dave about 9 years ago This comment was minimized by the moderator on the site 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 0 0 jlhall07 Dave about 8 years ago This comment was minimized by the moderator on the site [quote]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 SubBy Dave[/quote] I keep getting "can't assign to read-only property" concerning the path... Any idea? Reply 0 0 sara jlhall07 about 8 years ago This comment was minimized by the moderator on the site Im having tis problem too. Did you figure it out? Reply 0 0 jlhall07 sara about 8 years ago This comment was minimized by the moderator on the site Nothing yet... Haven't found any solution or had anyone suggest a fix. Sorry... Reply 0 0 Greg jlhall07 about 8 years ago This comment was minimized by the moderator on the site Me too. This was working 6 months ago, the last time I had to run it. Has anyone found the solution yet? If you had it running before and now it won't work could it be something to do with an update by Microsoft? This is really handy tool for my tasks and saves me loads of time. What could have changed that would cause Excel to start displaying this message all of a sudden? Being fairly new to VBA I have little idea where to begin analysing the logic. Kind regards, Greg. Glasgow, Scotland. Reply 0 0 fux Greg about 7 years ago This comment was minimized by the moderator on the site It seems that "Path" is now reserved so use just any other name and replace "Path", e.g. "Mypath". 0 0 Ma Hi jlhall07 about 5 years ago This comment was minimized by the moderator on the site Maybe you should change ReadOnly:=True to ReadOnly:=False, I have done and it was helpful Reply 0 0 Patrick Dave about 8 years ago This comment was minimized by the moderator on the site 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 0 0 Anand Darbha about 9 years ago This comment was minimized by the moderator on the site I want to combine all the sheets into one sheet where the headings are common...pls help Reply 0 0 Nazeer about 8 years ago This comment was minimized by the moderator on the site Hi, I tried using the above macros to collate few files, unfortunately no results... can some one help me getting rid of manually collating files. Reply 0 0 christian about 8 years ago This comment was minimized by the moderator on the site i have 112 excel sheets i want to put into a single sheet without copy and paste. Please help me out. Reply 0 0 Dileep about 8 years ago This comment was minimized by the moderator on the site I Have a workbook it contain around 250 Sheet . I need to Cobain in one sheet. please give me a solution Reply 0 0 Ginger41 about 8 years ago This comment was minimized by the moderator on the site Try this out...I got this from another site but unfortunately I can't remember the lady's name so my apologies for not giving her a mention, my bad" Combine multiple WB's in Excel: REMEMBER TO CHANGE MyPath = ! Sub Merge2MultiSheets() Dim wbDst As Workbook Dim wbSrc As Workbook Dim wsSrc As Worksheet Dim MyPath As String Dim strFilename As String Application.DisplayAlerts = False Application.EnableEvents = False Application.ScreenUpdating = False MyPath = "\\MyPath\etc\etc..." Set wbDst = Workbooks.Add(xlWBATWorksheet) strFilename = Dir(MyPath & "\*.xls", vbNormal) If Len(strFilename) = 0 Then Exit Sub Do Until strFilename = "" Set wbSrc = Workbooks.Open(Filename:=MyPath & "\" & strFilename) Set wsSrc = wbSrc.Worksheets(1) wsSrc.Copy After:=wbDst.Worksheets(wbDst.Worksheets.Count) wbSrc.Close False strFilename = Dir() Loop wbDst.Worksheets(1).Delete Application.DisplayAlerts = True Application.EnableEvents = True Application.ScreenUpdating = True End Sub Reply 0 0 Ghulam Ginger41 about 5 years ago This comment was minimized by the moderator on the site Hi I added the code into a module. Named the Excel book Masterfile. Where in the code do I add. thank you Reply 0 0 Niki Foster about 8 years ago This comment was minimized by the moderator on the site None of these worked for me I finally got this one to work. FYI I am using 2010 '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 Dim RowofCopySheet As Integer RowofCopySheet = 2 ' Row to start on in the sheets you are copying from ThisWB = ActiveWorkbook.Name path = "mypath....." ' Dont't forget to change this Application.EnableEvents = False Application.ScreenUpdating = False Set shtDest = ActiveWorkbook.Sheets(1) Filename = Dir(path & "\*.xls", vbNormal) If Len(Filename) = 0 Then Exit Sub Do Until Filename = vbNullString If Not Filename = ThisWB Then Set Wkb = Workbooks.Open(Filename:=path & "\" & Filename) Set CopyRng = Wkb.Sheets(1).Range(Cells(RowofCopySheet, 1), Cells(ActiveSheet.UsedRange.Rows.Count, ActiveSheet.UsedRange.Columns.Count)) Set Dest = shtDest.Range("A" & shtDest.UsedRange.SpecialCells(xlCellTypeLastCell).Row + 1) CopyRng.Copy Dest Wkb.Close False End If Filename = Dir() Loop Range("A1").Select Application.EnableEvents = True Application.ScreenUpdating = True MsgBox "Done!" End Sub Reply 0 0 michelle Niki Foster about 8 years ago This comment was minimized by the moderator on the site How do I edit this so that the data pulled in always starts on the top row? If I run this code twice, it adds the data to the end of my previous data (from the first run of the macro). Reply 0 0 orivera michelle about 8 years ago This comment was minimized by the moderator on the site Change this line: RowofCopySheet = 2 to RowofCopySheet = 1 Reply 0 0 Mohideen Niki Foster about 6 years ago This comment was minimized by the moderator on the site HI , I have multiple Excel File (single sheet) different folder with password protection. i want end of the day combine all data to one Master file. Every time I have to Enter password and open the file and copy paste to master file.. Kindly help me with VBA code for this please. |