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, you will find the detailed guidance in another tutorial: 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, 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.
    • To select non-adjacent sheets, hold the Ctrl key and click on each sheet tab individually.
  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 if any of those sheets 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 "Processed " & 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 (Code window), 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 macro in an Excel file, 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 70+ 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 other merge tools for Excel, you are welcome to download an evaluation version of Ultimate Suite.

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.

Available downloads

Macro to merge multiple Excel files (.xlsm file)
Ultimate Suite 14-day fully-functional version (.exe file)

Table of contents

249 comments

  1. 1. I need to calculate and post some figures into two different files?
    2. Can I email you the documents or files which I am talking about?
    3. I need to draw up in excel the cash flow.
    4. Is it possible for to add macro into IQ Retail so that i can able to
    calculate stock ordering Or how do I add up macro into IQ Retail?
    Can I be may be open up teamviewer and show me?

  2. The Macro way worked well for small workbooks. Only for workbooks that are really big with dashboards and charts and contain multiple worksheets I keep getting the following message:
    (The name "xxxx" already exists. Click Yes to use that version of the name, or click No to rename the version of "xxxx" you're moving or copying.

    the message keeps on popping up and it is preventing the merge of the workbooks. I guess it's because the files contain multiple worksheets and there might be some worksheets with the same name, not sure!

    Thank you,

    Anita Yalda

  3. Just an FYI - perhaps it's my older Excel version but I did the first steop, but even though I forgot to check off "Create a copy", my sheets remained in the source file anyway. Only dif - my source file closed itself. But when going back to my File Explorer, there it was, all still intact.

  4. Not sure but might have posted the sheet selection backwards.
    It says:
    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.

    I'm pretty sure it's the opposite.
    For NON-adjacent cells use Ctrl and for ADJACENT cells, use SHIFT.
    Also note is says in the non-adjacent part "will select all workbooks in between" - so I think it's just a typo on the word 'adjacent' and 'non-adjacent'

    • Hi Nan,

      You are absolutely right, just a silly typo - the 'non' prefix was in the wrong place :) Thank you very much for pointing this out, fixed!

  5. This worked beautifully! THANK YOU! Truly a lifesaver!

  6. None of these procedures seem to work on Mac Excel 2011. Many of the Excel tools do not seem to be available. The Vba macro doesn't seem to run. I have 100 separate Excel files that I want to merge into one.

    Neal

  7. i face an error every time i choose more than 2 files "Run Time error 1004 Application-Defined or object-defined error " and it only merge 2 files and open the third in another window >>>> i do not no if it maters but it can not rename the third worksheet . THANKS >>> Need your urgent support plz

  8. Is there a way to merge all the data on the same workbookor a file of your choosing? This macro creates a new workbook. I have other macros on the original workbook and would like to add all the data from the excel files into the current workbook. Thanks.

  9. Regarding the vba method, is it possible to build a code to only transfer the 1st tab from each excel file? Ex: 1st file contains Data1 tab and Result1 tab. 2nd excel file contains Data2 tab and Result2 tab. I'd like to create an excel file that will capture Data1 tab and Data2 tab only. This is just an example, the actual # of files are a lot more than 2. Thank you.

  10. Is there any possibility to get just the "Copy sheets" add-in wihtout having to buy the whole "Ultimate Suite"?

  11. The above VBA coding helped me but Is it possible to also merge hided sheets of the files when we merge the multiple file. Please help me with coding.

  12. Can I do the inverse? I mean, I have a file full of different dates but I need to unmerge them by date, each one in a individual file. (Not manually)

    • Hi Fernando,
      I hope I am right in thinking that ‘Split Table Wizard’ from ‘Ablebits Ultimate Suite’ might be helpful in dealing with your task since the tool provides an opportunity to extract the data from a table and direct those pieces of information to other locations, including newly created Excel workbooks: one per each. Please note that, if you work with dates, opting for ‘Key values’ in ‘Step 4’, will result in titles like ‘43739’ and ‘43956’ instead of ‘October 1, 2019’ and ‘May 5, 2020’ respectively.

  13. I love you. The macro worked PERFECTLY!

  14. Can this macro be enhanced to also include .csv files? Most downloads I perform save the files as .csv after which a merge into one spreadsheet would be invaluable.

    Thanks

    • Yes, I've tried it and it's working.
      You just need to edit the code so the file choose will accept also CSV.

  15. From the source files i need a particular sheet not all the sheets . Suppose three product files , (product1 , product2 and product3) all the files has many sheets like sales,employees, expenses and so on. But i need only sales sheets from all the 3 product files into one work book (not in one work sheet)

    • Hi Hari,

      Please see the last example in this tutorial that shows how to copy selected sheets to one workbook.

      • i need the same macro
        from the source files i need a particular sheet not all the sheet but i am not able to find your answer about the last example. could you provide the link plz

  16. Hi; when running the VBA script on mac excel 2011, version 14.7.0, I get the message "Runtime error 1004: The method GetOpenFileName to object '_Application' failed".

    Do you have any proposal for how to fix this?

    Best regards
    Wilhelm

    • Same problem on Excel V 16.21.1 on a Mac. Maybe this command is not supported on a mac?

      • Excel for mac doesn't support accessing the file system in the same way Windows does. VBA for Mac also doesn't debug very well. You're better off trying to run a script outside of VBA if using MacOS

  17. Dear Sir/ Madam

    I have to combine 30 excels files into one mail excel file. it is requested to kindly provide VBA code script.

    Regards

  18. Hi,
    Multiple sheets can be easily merge into one by a tool called as Power Query, its a separate tool needs to be download from Excel.
    Power query lets you to combine multiple sheets into one and helps you to compile the data.
    There is also a tool called as Power Pivot, which helps to apply Pivot on multiple sheets together.

    • Hey that is an interesting idea - could you elaborate?

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

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

    • Hi Dave,

      Exactly :) This whole article is about merging multiple Excel files into one, i.e. copying sheets from multiple workbooks into one workbook.

      If you are looking to merge data from multiple sheets into one sheet, you can try this VBA code or our Copy Sheets tool.

      • Hi Svetlana,
        My requirement is quite similar to Dave's. So i went to the link provided by you above, But even that VBA code does not consolidate the data into one sheet, but only creates different sheets in the master sheet. Can you please help me?

    • this is useless, copiying data from other workbook to this workbook , not into one sheet

      • Maybe it was not useful for you, but just worked for my purpose.

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)