Log in
x
or
x
x
Register
x

or
0
0
0
s2sdefault

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 Excelgood idea3
Easy, simple. <<Combine Multiple Workbooks>> is just one of 200 powerful features of Kutools    Free Download


Combine multiple workbooks to one workbook with VBA


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.


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

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 functions, makes your jobs easier. 

After installing Kutools for Excel, please do as below:(Free Download Kutools for Excel Now!)

1. Enable Excel and click Enterprise > Combine.
doc merge workbooks 1

There is a dialog pops out to remind you that the active workbook cannnot to be combined, click OK to continue. See screenshot:
doc merge workbooks 2

3.. In the Combine Worksheets wizard, select Combine multiple worksheets from workbooks into one workbook. Then click Next to go on. See screenshot:
doc merge workbooks 3

2. On step2 of wizard, all the opened workbooks and worksheets are listed in the list boxes,  click Add button to add other files or folders that you want to combine. Then in the poppding dialog to select the workbooks you want to combine, click Open to go back to Combine dialog, and you can check the workbooks or sheets you need to combine. See screenshot:

doc merge workbooks 4
doc merge multiple workbooks arrow1
doc merge workbooks 5
doc merge multiple workbooks arrow1
doc merge workbooks 6

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

  • (1.) If your workbooks contain some blank worksheets, you can keep or skip the blank worksheets as you need by clicking Skip drop down list.
  • (2.) 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 checking By inserting workbook name option.

4. Then click Finish button and choose a folder to merge and save the workbooks, and then a dialog pops out to ask you if to open the merged workbook. See screenshot:
doc merge workbooks 8

doc merge workbooks 9
Tip: 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 workbooks 5

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 workbooks 10

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.


arrow blue right bubble Related Articles:


Recommended Productivity Tools

Office Tab

gold star1 Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.

Kutools for Excel

gold star1 Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!

gold star1 200 New Features for Excel, Make Excel Much Easy and Powerful:

  • Merge Cell/Rows/Columns without Losing Data.
  • Combine and Consolidate Multiple Sheets and Workbooks.
  • Compare Ranges, Copy Multiple Ranges, Convert Text to Date, Unit and Currency Conversion.
  • Count by Colors, Paging Subtotals, Advanced Sort and Super Filter,
  • More Select/Insert/Delete/Text/Format/Link/Comment/Workbooks/Worksheets Tools...

Screen shot of Kutools for Excel

btn read more      btn download     btn purchase

Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
People in conversation:
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Hitesh · 2 months ago
    i want to combine data from multiple work books (excel file) whc includes 8 sheets
  • To post as a guest, your comment is unpublished.
    Lawrance · 2 months ago
    Error Line: Workbooks.Open Filename:=
  • To post as a guest, your comment is unpublished.
    ibra · 3 months ago
    how can I copy specific same cells for expamle (between A1-A15 for each excel sheet )from different files and paste all of them into a worksheet?
  • To post as a guest, your comment is unpublished.
    Shuk · 5 months ago
    Hi All
    I have successfully imported couple of excel spread sheets in one sheet by using below mentioned vb script:

    Sub GetSheets()
    Path = "Z:\.....\reports\"
    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

    However can anyone help me refining above script on how to import both the formats i.e. ".xls" and ".xlsx" of excel spread sheet by using single vb script.

    Any help would be much appreciated.
    • To post as a guest, your comment is unpublished.
      shuk · 4 months ago
      Got the solution for using both the formats i.e. ".xls" and ".xlsx" of excel spread sheet and code is given below:

      Sub GetSheet()
      Dim temp As String
      Path = "Z:\.....\reports\"
      Filename = Dir(Path & "*.xl??")
      Do While Filename ""
      Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
      temp = ActiveWorkbook.Name
      ActiveSheet.Name = ActiveSheet.Name
      ActiveWorkbook.Sheets(ActiveSheet.Name).Copy After:=ThisWorkbook.Sheets(1)
      Workbooks(Filename).Close
      Filename = Dir()
      Loop
      End Sub
  • To post as a guest, your comment is unpublished.
    Shuk · 5 months ago
    I have used below mentioned script and it was successful :-)

    Sub GetSheets()
    Dim temp As String
    Path = "Z:\.....\"
    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

    However facing issue with two different format of excel spread sheets i.e. [b]"xls"[/b] and [b]"xlsx"[/b] which i would like to import. Any help would be greatly appreciated.