How to Combine Multiple Excel Files into One Excel Workbook

I got a call from a friend who wanted to combine multiple Excel files into one Excel workbook. He had a lot of files in a folder and he wanted to get all the worksheets from all the workbooks into one single workbook.

While this can be done manually, it would be time-consuming and error-prone.

However, a simple VBA code can do this in a few seconds.

Combine Multiple Workbooks into One Excel Workbook - Image Orange

Combine Multiple Excel Files into One File

Here is the code that can combine multiple Excel workbooks in a specified folder into a single Excel workbook:

Sub ConslidateWorkbooks()
'Created by Sumit Bansal from https://trumpexcel.com
Dim FolderPath As String
Dim Filename As String
Dim Sheet As Worksheet
Application.ScreenUpdating = False
FolderPath = Environ("userprofile") & "\Desktop\Test\"
Filename = Dir(FolderPath & "*.xls*")
Do While Filename <> ""
 Workbooks.Open Filename:=FolderPath & Filename, ReadOnly:=True
 For Each Sheet In ActiveWorkbook.Sheets
 Sheet.Copy After:=ThisWorkbook.Sheets(1)
 Next Sheet
 Workbooks(Filename).Close
 Filename = Dir()
Loop
Application.ScreenUpdating = True
End Sub

How to Use this Code?

Here are the steps to use this code:

  • Put all the Excel files that you want to combine into a folder. For the purpose of this tutorial, I have created a folder named Test and have six files in it (4 Excel workbooks and 1 Power Point and Word each).Combine Multiple Workbooks into One Excel Workbook - test Folder
  • Open a new Excel workbook.
  • Press ALT + F11 (or go to Developer –> Code –> Visual Basic). This will open the Visual Basic Editor.
  • In the VB Editor, in the Project Editor, right-click on any of the objects for the workbook and go to Insert –> Module. This will insert a module for the workbook.Combine Multiple Excel files into One Excel Workbook - insert module
  • Double click on the module. It will open the code window on the right.
  • Copy and paste the above code into the code window.Combine Multiple Excel files into One Excel Workbook - code
  • In the code, you need to change the following line of code:
    FolderPath = Environ("userprofile") & "\Desktop\Test\"

    In this line, change the part in double quotes (highlighted in orange) with the location of the folder in which you have the files that you want to combine. In the code used above, the folder is on the Desktop. In case you have it in some other location, specify that path here.

  • Place the cursor anywhere in the code and click on the green play button in the Toolbar options (or press the F5 key).Combine Multiple Workbooks into One Excel Workbook - run code

This will run the code and all the worksheets from all the Excel files in the folder would get consolidated into a single workbook.

Combine Multiple Excel files into One Excel Workbook - demo

How this Code Works?

  • The code uses the DIR function to get the file names from the specified folder.
  • The following line assigns the first excel file name to the variable ‘Filename’.
    Filename = Dir(FolderPath & “*.xls*”)
  • Then the Do While loop is used to check whether all the files have been covered.
  • Within the ‘Do While’ loop, ‘For Each’ loop is used to copy all the worksheets to the workbook in which we are running the code.
  • At the end of the Do Loop, following line of code is used: Filename = Dir(). It assigns the next Excel file name to the Filename variable and the loop starts again.
  • When all the files are covered, DIR function returns an empty string, which is when the loop ends.

Here is an explanation of the DIR function in the MSDN library:

Dir returns the first file name that matches pathname. To get any additional file names that match pathname, call Dir again with no arguments. When no more file names match, Dir returns a zero-length string (“”).

Have you ever tried something of this sort using VBA? Do share what you did and we all can learn from it.

Save Crazy Amount of Time Using VBA. Check out the Excel VBA COURSE.

You May Also Like the Following Excel Tutorials:

  • Chams says:

    What about copying different sheets from different excel files to one sheet by adding the new data to the old one ?

  • Tammy Davis says:

    Thank you so much for this VBA code! Excellent time-saver!

  • Prabhjeet Singh says:

    Hi, I have to copy 4 sheets from different excel files in one excel.
    Excel name in which i have to copy my data= Summary

    Data copy from below listed excels
    Excel 1= Los Angeles CCAP ASE Job Log Backup 15-11-18
    Excel 2= Anaheim CCAP ASE Job Log Backup 15-11-18
    Excel 3= BAY-North CCAP ASE Job Log Backup 15-11-18
    Excel 4= San Diego CCAP ASE Job Log Backup 15-11-18

    Now from every file, I have to copy only 1 sheet. names of sheets are mentioned below.
    Excel 1 sheet name= LA
    Excel 2 sheet name= Anaheim
    Excel 1 sheet name= SD
    Excel 1 sheet name= BayNorth

    Please help I have to submit a report to my manager.

  • Keith says:

    I have over 2000 files to combine. I did a test run of 100 and the result was garbage.

  • prerna says:

    how to combine three workbook in one workbook easy and quick

  • Kaushik says:

    I am getting error mentioning Run time error 52. Bad file name or number. For your information I have saved the files on my Desktop with a folder name as Test. Please suggest

  • Sneha says:

    I am getting an error as bad file name,can someone help me with this error,would be really thankful!

  • Lisa says:

    It doesn’t work for me, I have “bad file name or number”. do you know how to fix it?

  • umar gillani says:

    helpful

  • Rishi Raj Gautam says:

    very useful ,I like it very much !

  • sheetz says:

    When i press f5, nothing happens -! can anyone pls help ?

  • Aly June says:

    Attempting with CSV files. =) will let you know.

  • Tonight im suarez says:

    I have a query . Suppose I have to merge 652 workbooks into one. The filenames are 1.xlsx to 652.xlsx and i want to merge them in ascending order of their filenames only . Will this code merge it according to their sorted name in the folder or not ??

  • sid kumar says:

    Hi Sumit, need your assistance with the above macro.

    This works good if you want to combine multiple sheets in 1 workbook.

    I need a solution where the following steps need to be done :

    1. There are around 4000 employees and the workbook will be each employee code wise
    2. There are 18 folders with workbooks with 1 worksheet for each employee.. there is a possibility that for an employee one of the workbooks will not be there
    3. The macro should create a workbook with the emp code then across the 18 folders should check whether workbook is available for the employee ID and if yes
    4. Copy and paste it in the created workbook and save.
    5. Additionally there are 3 workbooks by default which need to be added for all employees

    What i do not know to edit the above macro is:

    1. How do i build a For loop for an employee list
    2. If there is no specific excel workbook in one of the folder then it should not throw an error

    Please help. I can communicate on your official email id too and will be happy if you can provide a solution at the earliest even if it is chargeable.

    Regards

  • Jon Standring says:

    That’s great thanks. It would be even better if it named the sheets after the original file names.

  • PailHorse says:

    Still usable. You need to place a “/” after your path if you copy it in.

  • Steve says:

    The code is really work for me using in Excel 2016 after few experiments.
    Some tips for those who are struggling about getting error message:
    1) The code is worked for both xls and xlsx, don’t need to modify it.
    2) The code ONLY works for worksheet, do try to move chart, you will get error. (anyone know how to move all together?)
    3) Try 1 or 2 workbooks first to see whether it is work for you or not.

    Hope these help you all 🙂

  • Ghulam says:

    Wow first one actually worked:)

  • Jiggs Casey says:

    This did not work. Yes it combines 2 workbooks, but it destroys the functionality of the formulas in the process. Now, this could be me making some bad choices when it prompts me while the macro is running. Idk. Example: while macro was running I got several “name ‘use lists’ already exists. Yes to keep no to rename it something else” messages (sub ‘use lists’ with various other names). And regarding other formulas being destroyed, example: a simple =’TAB’!A1&” “&’TAB’!A2 type of formula is now bloated with file directory info. So it reads like:
    =’C:UserEtcpathTAB’!A1&” “&’C:UserEtcpathTAB’!A2
    That last one I can fix by deleting the stupid file directory info, formula by formula. As for the whole yes or no to renaming stuff that already exists… I’m at a loss.

    TL;DR I don’t know VBA & this formula didn’t work in my scenario.

  • Slamat Ale says:

    Sumit Bansal you Rock <3

  • J2 says:

    Thanks for posting this! I’m getting “Run-time error ’52’: Bad file name or number” – anyone know how I can overcome this?

    • lb says:

      change .xls to .xl
      I removed the “environment” as well

  • Akbar Mahfuz alam says:

    Need multiple files into one single sheet.

  • Benjamin Leung says:

    Hi, I was able to get the script above to work.

    How can I modify this script to combined Sheets with the name “Sheet1” from multiple file into one file.

    • Maria Angelova says:

      so how did you ended up making it work???

  • What am I missing? I keep getting a run time error 52 (bad file name or number) on this line: Filename = Dir(FolderPath & “*.xlsm*”). I changed it to .xlsm for Excel 2013 for macro.

    • Maria Angelova says:

      Ugh…me too!!!

  • Pam says:

    also, can I only combine page 1 of the workbooks to be combined into a single workbook?

  • Pam says:

    If my files are on a network drive, do I remove the “Environ” part of the FolderPath?

  • CA Rahul Agrawal says:

    It’s not working for me..!! When i am pressing F5…no combination of sheets..>!!

  • Lazarus says:

    Thank you for the tip.
    I have a question related to the handling the files with macros (*.xlsm) since the code will pick these files as well during the run. It seems that the macros will not be copied along with the worksheets and therefore some of the formulas may not work in the new workbook.

    • Sumit Bansal says:

      Thanks for commenting Lazarus.. You’re right! This wouldn’t pick up the macros while copying the sheets

      • Vinay says:

        is there any way if the multiple files can be consolidated into one single tab instead of multiple tabs?

      • Parmendra Nirwal says:

        Hi Sumit,
        Also it is not working for me for “xlsx” file. It is showing debugg at Filename = Dir(FolderPath & “*.xlsx*”). Please get a solution. Thanks

        • Maria Angelova says:

          I am getting the same error…..

          • manoj says:

            Here When the Path In the Folder Is Copied It Must Start Form The “Desktop” & Not As “C:”

            If Rectify This Then It Automatically Works When pressing “F5”

        • manoj says:

          Here When the Path In the Folder Is Copied It Must Start Form The “Desktop” & Not As “C:”
          If Rectify This Then It Automatically Works When pressing F5

  • Anand Kumar says:

    Thanks for this tip Sumit………….

    But it create multiple sub sheets of multiple files…what should to do if we need all multiple files into only one single sub sheet…………

    Pls. help.

    • Sumit Bansal says:

      Hello Anand, By what I understand, You want to combine all the sheets from all the workbooks into a single sheet. This may be limited by the number of rows in a worksheet. It would be helpful if you could share a sample file

      • Anand Kumar says:

        Hi Sumit,

        Firstly Thanks for concern.

        I have send an email to you for further clarification on it. Pls. have a look.

        • Anand Kumar says:

          Hi Sumit………..

          Pls. help on this…………

  • >