Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

How to combine multiple workbooks into one master workbook in Excel?

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.


Easily combine multiple workbooks into one in Excel:

The Combine workbooks utility of Kutools for Excel can help you to easily combine multiple Excel files into one. Besides, it allows you to only combine the specified worksheets of certain Excel files to a master workbook easily. Download the full feature 60-day free trail now!

 
 

Combine multiple workbooks to 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.

Office Tab - a real time-saver add-in!

Display all opened documents in a single window to help quickly finding a needed document at a glance.

Full feature free trial 45-day. Compatible with every version of Excel, Word and PowerPoint.

              KNOW MORE ABOUT THE ADD-IN >

 

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 & "*.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
	

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

Kutools for Excel

It is a handy Excel add-in which gathers more than 300 advanced features to simplify your work in Excel operations. With it, you can easily:

  • Combine Multi-workbooks;
  • Count/sum cells by color;
  • Split cells;
  • Batch remove spaces;
  • More features...

FREE DOWNLOAD NOW!

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.

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.

Does this Combine workbooks utility help? Let's download and try the full feature free trial 60-day!        

 

Recommended Productivity Tools for Microsoft Office

Office Tab - Tabbed Browsing, Editing, and Managing in Excel/Word/PowerPoint:

Office Tab brings the tabbed interface as seen in web browsers such as Google Chrome, Internet Explorer new versions and Firefox to Microsoft Excel/Word/PowerPoint. It will be a time-saving tool and irreplaceble in your work. See below demo:

ot1

Kutools for Excel - Combines More Than 300 Advanced Functions and Tools for Microsoft Excel:

Kutools for Excel is a handy Excel add-in with more than 300 advanced features to simplify various kinds of complicated tasks into a few clicks in Excel. For example:

  • Combine multiple worksheet or workbooks into one workbook
  • Count and sum cells based on background/font color
  • Split cell contents into multiple rows/columns by space/comma/delimiter
  • Batch Remove leading spaces in cells
kte

Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Dezignext Technologies · 1 months ago
    I like using this technique better than using traditional "3D Formula" techniques in Excel.
  • To post as a guest, your comment is unpublished.
    Jonel · 2 months ago
    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.

    Can we have the module for VBA that above scene will work,
    • To post as a guest, your comment is unpublished.
      crystal · 1 months ago
      Hi Jonel,
      The following code can help you solve the problem. You need to replace folder path and "Sheet1, Sheet3" with the specified folder path and worksheets as you need.

      Sub MergeSheets2()
      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
  • To post as a guest, your comment is unpublished.
    Chris · 3 months ago
    When I run this, each sheet in the new workbook is being named based off of the sheet names of the original document rather than the filenames. Any idea what I might be doing wrong?
    • To post as a guest, your comment is unpublished.
      crystal · 1 months ago
      Hi Chris,
      If you want to distinguish which worksheets in the master workbook came from where after merging, please apply the below VBA code to solve the problem.

      Sub MergeWorkbooks()
      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
  • To post as a guest, your comment is unpublished.
    Owen · 6 months ago
    It didnt work for me then I realized my files are .xlsx, so added the missing "x" to the Filename line.
  • To post as a guest, your comment is unpublished.
    Justin · 9 months ago
    This worked for me but I had to make sure I have to put "\" at the end of my path. Initially, I didn't have it and it wouldn't work.