Combine multiple sheets workbook into one single sheet or workbook

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.

  • Method A:
  • Method B:
  • Method C:


Combine multiple workbooks Into one workbook with Move or Copy function

If 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 VBA

If 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

Sub GetSheets()
'Updated by Extendoffice 2019/2/20
Path = "C:\Users\dt\Desktop\dt kte\"
Filename = Dir(Path & "*.xlsx")
  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
	

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):

Sub MergeWorkbooks()
'Updated by Extendoffice 2019/2/20
Dim xStrPath As String
Dim xStrFName As String
Dim xWS As Worksheet
Dim xMWS As Worksheet
Dim xTWB As Workbook
Dim xStrAWBName As String
On Error Resume Next
xStrPath = "C:\Users\DT168\Desktop\KTE\"
xStrFName = Dir(xStrPath & "*.xlsx")
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set xTWB = ThisWorkbook
Do While Len(xStrFName) > 0
    Workbooks.Open Filename:=xStrPath & xStrFName, ReadOnly:=True
    xStrAWBName = ActiveWorkbook.Name
    For Each xWS In ActiveWorkbook.Sheets
    xWS.Copy After:=xTWB.Sheets(xTWB.Sheets.Count)
    Set xMWS = xTWB.Sheets(xTWB.Sheets.Count)
    xMWS.Name = xStrAWBName & "(" & xMWS.Name & ")"
    Next xWS
    Workbooks(xStrAWBName).Close
    xStrFName = Dir()
Loop
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

VBA code 3: Merge specified worksheets of workbooks into a master workbook:

Sub MergeSheets2()
'Updated by Extendoffice 2019/2/20
Dim xStrPath As String
Dim xStrFName As String
Dim xWS As Worksheet
Dim xMWS As Worksheet
Dim xTWB As Workbook
Dim xStrAWBName As String
Dim xI As Integer
On Error Resume Next

xStrPath = " C:\Users\DT168\Desktop\KTE\"
xStrName = "Sheet1,Sheet3"

xArr = Split(xStrName, ",")

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set xTWB = ThisWorkbook
xStrFName = Dir(xStrPath & "*.xlsx")
Do While Len(xStrFName) > 0
Workbooks.Open Filename:=xStrPath & xStrFName, ReadOnly:=True
xStrAWBName = ActiveWorkbook.Name
For Each xWS In ActiveWorkbook.Sheets
For xI = 0 To UBound(xArr)
If xWS.Name = xArr(xI) Then
xWS.Copy After:=xTWB.Sheets(xTWB.Sheets.count)
Set xMWS = xTWB.Sheets(xTWB.Sheets.count)
xMWS.Name = xStrAWBName & "(" & xArr(xI) & ")"
Exit For
End If
Next xI
Next xWS
Workbooks(xStrAWBName).Close
xStrFName = Dir()
Loop
Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

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 workbook

Fortunately, 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:

  • 1) All workbooks and worksheets are listed in the dialog box;
  • 2) For the worksheets you want to exclude from merging, just uncheck it;
  • 3) Blank worksheets are excluded automatically;
  • 4) The original file name will be added as prefix to the sheet name after merging;
  • For more functions of this feature, please visit here.

  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.
No longer need memorizing formulas and VBA codes, give your brain a rest from now on.
Complicated and repeated operations can be done a one-time processing in seconds.
Reduce thousands of keyboard & mouse operations every day, say goodbye to occupational diseases now.
Become an Excel expert in 3 minutes, help you quickly get recognized and a pay raise promotion.
110,000 highly effective people and 300+ world-renowned companies' choice.
Make your $39.0 worth more than $4000.0 training of others.
Full feature free trial 30-day. 60-Day Money Back Guarantee without reason.

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.