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

or
0
0
0
s2smodern

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

Combine multiple workbooks to one workbook with Move or Copy command

Easily combine multiple workbooks to one workbook with Kutools for Excel


Easily combine multiple worksheets/workbooks into one worksheet / workbook:

Combine multiple worksheets or workbooks into one single worksheet or workbook may be a huge task in your daily work. But, if you have Kutools for Excel, its powerful utility – Combine can help you quickly combine multiple worksheets, workbooks into one worksheet or workbook

Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial Now!


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:

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.

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:

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:


Easily 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 60 days.

1. Enable Excel 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, and then click the Next button. See screenshot:

2. Then you can see all opened workbooks and worksheets are listed in the Combine Worksheets - Step 2 of 3 dialog box. Click the Add > File / Folder to add other Excel files or folders whih containing the Excel files you will combine. After selecting files, click the Next button to go ahead. See screenshot:

doc merge multiple workbooks arrow1

3. In the Combine Worksheets - Step 3 of 3 dialog box, please do the following settings:

  • 3.1 You can exclude all blank worksheets from merging by selecting Skip from the When encountering a blank worksheet drop down list.
  • 3.2 Check the First row of each worksheet (Comment) option to insert worksheet information of every combined worksheet.
  • 3.3 You can rename the new combined worksheets by inserting the workbook names before or after the original worksheet names, or just keep the original worksheet names with unchecking the By inserting workbook name option.
  • 3.4 Click the Finish button.

4. Then choose a folder to save the combined workbook, and open it directely as you need. Now all specified workbooks are combined into one.

Notes:

1. You will be asked if you want to save this scenario. Click Yes to save as you need, then enter the name of the scenario, (see following screenshots). After saving the scenario, you only need to click Scenario button to choose the name of the scenario on step2 wizard to use it directly.

2. Worksheets in selected workbooks have been merged into a new workbook, and the worksheets are named with the workbook name before or after the original worksheet name if you have checked the By inserting workbook name option. Meanwhile, a new worksheet named Kutools for Excel is generated before all worksheets, which lists the workbooks with detailed information of each worksheets. See screenshot:

3. 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.

Tip.If you want to have a free trial of this utility, please go to download the software freely first, and then go to apply the operation according above steps.


Office Tab - Tabbed Browsing, Editing, and Managing of Workbooks in Excel:

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

Click for free trial of Office Tab!

Office Tab for Excel

Demo: Easily combine multiple workbooks to one workbook with Kutools for Excel




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 300 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.
    Gnanesh · 2 years ago
    Thank you very much for the script for combining workbooks!
  • To post as a guest, your comment is unpublished.
    Hoang · 2 years ago
    The above code works well, but it seems that it doesn't follow any certain order, in my folder, each excel file ( before merging ) has 1 sheet with same name,for exp: book1 contains sheet named A, book2 also contains sheet named A ,....then after merging by VBA, the combined workbook contains sheets named A, A(1), A(2), A(3),...in random order, A(1) actually doesn't belongs to initial book1. So it's hard for me to identify, I can't know each sheet originally belongs to which file. The result I expect is that all sheets will be combine in name order of original separated file

    Perhaps i can't explain my point well, if someone has same situation or can understand me, then pls kindly upgrade vba code that can combine sheets in order of alphabet name or ascending number ?
  • To post as a guest, your comment is unpublished.
    wooly · 2 years ago
    I'm new to Excel and am struggling to identify what I need to personalise in this code to make it work. Obviously "path" but is that it? as I'm getting errors on the two "set copyrng" and "set dest" row.

    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.Ena bleEvents = False
    Application.Scr eenUpdating = 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).R ange(Cells(Rowo fCopySheet, 1), Cells(ActiveShe et.UsedRange.Ro ws.Count, ActiveSheet.Use dRange.Columns. Count))
    Set Dest = shtDest.Range(" A" & shtDest.UsedRan ge.SpecialCells (xlCellTypeLast Cell).Row + 1)
    CopyRng.Copy Dest
    Wkb.Close False
    End If

    Filename = Dir()
    Loop

    Range("A1").Sel ect

    Application.Ena bleEvents = True
    Application.Scr eenUpdating = True

    MsgBox "Done!"
    End Sub
  • To post as a guest, your comment is unpublished.
    Gaurav Sethi · 2 years ago
    Hi,

    My name is Gaurav, I have 10 Excel workbook & i want to add all file in one workbook. Please suggest.
  • To post as a guest, your comment is unpublished.
    Shubham · 2 years ago
    GREAT STUFF!! You saved me a lot of effort!