Nov
8

How to merge multiple Excel files into one

The tutorial shows three ways to combine Excel files into one: by copying sheet tabs, running VBA, and using the Copy Worksheets tool.

It is a lot easier to process data in a single file instead of switching between numerous sources. However, merging multiple Excel workbooks into one file could be a cumbersome and long process, especially if the workbooks you need to combine contain multiple worksheets. So, how would you approach the problem? Will you be coping sheets manually or with VBA code? Or, do you use one of the specialized tools to merge Excel files? Below you will find a few good ways to handle this task.

Note. In this article, we are going to look at how to copy sheets from multiple Excel workbooks into one workbook. If you are looking for a quick way to copy data from several worksheets into one sheet, please check out the following article for the detailed guidance:
How to merge multiple sheets into one.

How to merge two Excel files into one by copying sheets

If you have just a couple of Excel files to merge, you can copy or move sheets from one file to another manually. Hers's how:

  1. Open the workbooks you wish to combine.
  2. Select the worksheets in the source workbook that you want to copy to the main workbook.

    To select multiple sheets, use one of the following techniques:

    • To select adjacent sheets, hold the Ctrl key and click on each sheet tab individually.
    • To select non-adjacent sheets, click on the first sheet tab that you want to copy, press and hold the Shift key, and then click on the last sheet tab. This will select all worksheets in between.
  3. With all worksheets selected, right click on any of the selected tabs, and then click Move or Copy….
    Select the worksheets you want to merge.
  4. In the Move or Copy dialog box, do the following:
    • From the Move selected sheets to book drop-down list, select the target workbook into which you want to merge other files.
    • Specify where exactly the copied sheet tabs should be inserted. In our case, we choose the move to end option.
    • Select the Create a copy box if you want the original worksheets to remain in the source file.
    • Click OK to finish the merge process.

    Copy sheet tabs from one Excel file to another.

The screenshot below shows the result - sheets from two Excel files combined into one. To merge tabs from other Excel files, repeat the above steps for each workbook individually.
Sheets from two Excel files are merged into one.

When coping sheets manually, please be aware of the following limitation imposed by Excel: it is not possible to move or copy a group of sheets that contains a table. In this case, you will have to either convert a table to a range or use one of the following methods that do not have this limitation.

How to merge Excel files with VBA

If you have multiple Excel files that have to merged into one file, a faster way would be to automate the process with a VBA macro.

Below you will find the VBA code that copies all sheets from all Excel files that you select into one workbook. This MergeExcelFiles macro is written by Alex, one of our best Excel gurus.

Important note! The macro works with the following caveat - the files to be merged should not be open physically or in memory. In such a case, you will get a run-time error.
Sub MergeExcelFiles()
    Dim fnameList, fnameCurFile As Variant
    Dim countFiles, countSheets As Integer
    Dim wksCurSheet As Worksheet
    Dim wbkCurBook, wbkSrcBook As Workbook

    fnameList = Application.GetOpenFilename(FileFilter:="Microsoft Excel Workbooks (*.xls;*.xlsx;*.xlsm),*.xls;*.xlsx;*.xlsm", Title:="Choose Excel files to merge", MultiSelect:=True)

    If (vbBoolean <> VarType(fnameList)) Then

        If (UBound(fnameList) > 0) Then
            countFiles = 0
            countSheets = 0

            Application.ScreenUpdating = False
            Application.Calculation = xlCalculationManual

            Set wbkCurBook = ActiveWorkbook

            For Each fnameCurFile In fnameList
                countFiles = countFiles + 1

                Set wbkSrcBook = Workbooks.Open(Filename:=fnameCurFile)

                For Each wksCurSheet In wbkSrcBook.Sheets
                    countSheets = countSheets + 1
                    wksCurSheet.Copy after:=wbkCurBook.Sheets(wbkCurBook.Sheets.Count)
                Next

                wbkSrcBook.Close SaveChanges:=False

            Next

            Application.ScreenUpdating = True
            Application.Calculation = xlCalculationAutomatic

            MsgBox "Procesed " & countFiles & " files" & vbCrLf & "Merged " & countSheets & " worksheets", Title:="Merge Excel files"
        End If

    Else
        MsgBox "No files selected", Title:="Merge Excel files"
    End If
End Sub

How to add this macro to your workbook

If you'd like to insert the macro in your own workbook, perform these usual steps:

  1. Press Alt + F11 to open the Visual Basic Editor.
  2. Right-click ThisWorkbook on the left pane and select Insert > Module from the context menu.
  3. In the window that appears, paste the above code.

For the detailed step-by-step instructions, please see How to insert and run VBA code in Excel.

Alternatively, you can download the workbook with MergeExcelFiles macro, open it alongside your target workbook (enable macro if prompted), then switch to your own workbook and press Alt + F8 to run the macro. If you are new to using macros in Excel, please follow the detailed steps below.

How to use the MergeExcelFiles macro

Open the Excel file where you want to merge sheets from other workbooks and do the following:

  1. Press Alt + F8 to open the Macro dialog.
  2. Under Macro name, select MergeExcelFiles and click Run.
    Run the MergeExcelFiles macro.
  3. The standard explorer window will open, you select one or more workbooks you want to combine, and click Open. To select multiple files, hold down the Ctrl key while clicking the file names.

Depending on how many files you've selected, allow the macro a few seconds or minutes to process them. After the macro completes, it will notify you how many files have been processed and how many sheets have been merged:
The result of executing the MergeExcelFiles macro

Combine multiple Excel files into one with Ultimate Suite

If you are not very comfortable with VBA and looking for an easier and faster way to merge Excel files, have a look at the Copy Sheets tool, one of 60+ time saving features included with our Ultimate Suite for Excel.

With the Ultimate Suite, merging multiple Excel workbooks into one is as easy as one-two-three (literally, only 3 quick steps). You don't even have to open all of the workbooks you want to combine.

  1. With the master workbook open, go to the Ablebits Data tab > Merge group, and click Copy Sheets > Selected Sheets to one Workbook.
    Merge multiple workbooks into one.
  2. In the Copy Worksheets dialog window, select the files (and optionally worksheets) you want to merge and click Next.
    Select the worksheets you want to merge.

    Tips:

    • To select all sheets in a certain workbook, just put a tick in the box next to the workbook name, all the sheets within that Excel file will be selected automatically.
    • To merge sheets from closed workbooks, click the Add files… button and select as many workbooks as you want. This will add the selected files only to the Copy Worksheets window without opening them in Excel.
    • To copy only a specific area in a certain workbook, hover over the sheet name with your mouse, then click the Collapse Dialog icon  Collapse Dialog icon and select the desired range. By default, all data is copied.
  3. Select one or more additional options, if needed, and click Copy. The screenshot below shows the default settings: Paste all (formulas and values) and Preserve formatting.
    Choose how you want to copy worksheets.

Allow the Copy Worksheets wizard a few seconds for processing and enjoy the result!
Sheets from multiple Excel workbooks are merged into one file.

To have a closer look at this and a other merge tools for Excel, you are welcome to download 14-day trial version of the Ultimate Suite. If you like the tools and decide to get a license, don't miss the 15% off coupon code that we provide exclusively to our blog readers: AB14-BlogSpo

Other ways to merge Excel sheets and combine data

The above examples have demonstrated the best techniques to merge multiple Excel files into one. For more ways to combine sheets in Excel, please check out the following resources.

3 Responses to "How to merge multiple Excel files into one"

  1. Dave says:

    The VBA Macro does not merge the data into one sheet. It only brings over sheets from other workbooks.

  2. Dave says:

    The VBA code will merge data into one from multiple workbooks.

Post a comment



Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!
Excel add-ins and Outlook tools - Ablebits.com
Ultimate Suite for Excel Professionals
 
 
60+ professional tools for Excel 2016-2007 to do your daily work
Incredible product, even better tech support…AbleBits totally delivers!
Deborah Bryant
Anyone who works with Excel is sure to find their work made easier
Jackie Lee
The best spent money on software I've ever spent!
Patrick Raugh
Ablebits is a fantastic product - easy to use and so efficient.
Debra Celmer
Excel is at its best now
Annie C.
I don't know how to thank you enough for your Excel add-ins
Jennifer Morningstar
Anybody who experiences it, is bound to love it!
Kumar Nepa
AbleBits suite has really helped me when I was in a crunch!
Nelda Fink
I have enjoyed every bit of it and time am using it
Christian Onyekachi Nwosu
It's the best $100 we've ever spent!
Mike Cavanagh
I love the program, and I can't imagine using Excel without it!
Robert Madsen
One word… WOW!
Dave Brown
Love the products!
David Johnston
It is like having an expert at my shoulder helping me…
Linda Shakespeare
Your software really helps make my job easier
Jeannie C.
Thanks for a terrific product that is worth every single cent!
Dianne Young
I love your product
Brad Gibson
Awesome!!!
Sheila Blanchard