banner



Home  ›  How To Combine Multiple Excel Files Into One Worksheet Using Macro

How To Combine Multiple Excel Files Into One Worksheet Using Macro

Written By Sunday, February 13, 2022 Add Comment Edit

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 exist time-consuming and error-prone.

Withal, 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 Equally Cord            Dim Sheet Every bit Worksheet            Awarding.ScreenUpdating = False            FolderPath = Environ("userprofile") & "DesktopTest"            Filename = Dir(FolderPath & "*.xls*")            Do While Filename <> ""                          Workbooks.Open Filename:=FolderPath & Filename, ReadOnly:=True                          For Each Sheet In ActiveWorkbook.Sheets                          Sheet.Re-create Later on:=ThisWorkbook.Sheets(one)                          Next Canvas                          Workbooks(Filename).Close                          Filename = Dir()            Loop            Application.ScreenUpdating = Truthful            Cease Sub

How to Use this Code?

Here are the steps to apply this lawmaking:

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 lawmaking uses the DIR function to go the file names from the specified folder.
  • The post-obit line assigns the first excel file name to the variable 'Filename'.
    Filename = Dir(FolderPath & "*.xls*")
  • And then the Do While loop is used to bank check whether all the files accept been covered.
  • Inside 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 cease of the Exercise Loop, following line of code is used:Filename = Dir().It assigns the next Excel file name to the Filename variable and the loop starts over again.
  • When all the files are covered, DIR function returns an empty cord, which is when the loop ends.

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

Dir returns the first file name that matches pathname. To become any additional file names that match pathname, call Dir once again with no arguments. When no more than file names lucifer, Dir returns a zero-length cord ("").

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

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

You lot May Also Similar the Following Excel Tutorials:

  • How to Combine Data from Multiple Workbooks into I Excel Table (using Ability Query).
  • Rapidly Create Summary Worksheet with Hyperlinks in Excel.
  • How to Create and Use an Excel Add-in.
  • How to Run a Macro.
  • xx Useful Excel Macro Examples.

Excel Ebook Subscribe

Get 51 Excel Tips Ebook to skyrocket your productivity and get work washed faster

98 thoughts on "How to Combine Multiple Excel Files into One Excel Workbook"

  1. Brilliant thank you!

  2. Highly frustrating – simply can't get the damn thing to work….

    • I had this problem too but fixed information technology as follows,

      1. Salve the document equally .xlxm
      2. Make sure you lot have a "/" at the cease of your directory path

      • sorry .xlsm

  3. The code works but the filepath string requires all of the "" in the consummate file path. For example: C:UsersUser1DesktopExcelfiles*.xls*. This best manner to solve this is:

    When you modify the FolderPath = Environ("userprofile") & "DesktopTest" make sure you include the pre and post forwards slashes "" to the folder path containing the excel sheets to be imported.

    When you are debugging right click on the Folderpath and Filename variable and set them to the Watchlist with the Break when value changes box checked. When you run the program you lot can verify those 2 variables are updating correctly.

  4. Thanks and so much, worked perfectly.

  5. Not working,Information technology is showing debugg at Filename = Dir(FolderPath & "*.xlsx*").
    Can you please provide a set

  6. For this code to piece of work some of you will likely need more than than the suggested file path of:

    FolderPath = Environ("userprofile") & "DesktopTest"

    Environ("userprofile") will just provide the kickoff office of a filepath. In my case information technology was: C:UsersWF. There were several subfolders "along the path" before getting to the folder I used for the files to exist combined. Use this instance equally the path to the right folder:

    folderpath = "C:UsersWFDocumentsFolder1Folder2Folder3FolderWithFiles

    For a convenient mode to get the correct path use this lawmaking to place the path on a worksheet. Then copy/paste into the sub procedure.

    Sub Path_FileName()
    Dim strPath As String
    strPath = ActiveWorkbook.FullName
    ActiveCell.Value = strPath
    End Sub

    Make this adjustment if yous need to. This is some very useful code.

  7. I was getting fault 52 besides.

    "DESKTOP TEST" = LOCATION OF FILE

    I changed this cord of code (moved the parenthesis) from the beneath and it worked:

    CHANGED FROM
    FolderPath = Environ("userprofile") & "DesktopTest"

    Inverse TO
    FolderPath = Environ("userprofile" & "DesktopTest")

  8. Big thank you!! I had constitute various other versions of this code but this is the first one that worked for my version of Excel. Needed to combine over 30 spreadsheets and it saved me lots of time!

  9. I am getting error 52 when I run the code.

  10. Cheers alot. Information technology was very helpful. I am a options trader needed it to backtest the things. So thanks once again. Let me know how can I give information technology dorsum to you.

  11. Many thanks for this! Forty spreadsheets … into one without copy/pasting each sheet. Phew.

  12. how can i add in the header in the agile worksheet before I run this code? or i tin can do information technology at the same time?

  13. I have followed the instructions advisedly but i go an error of Bad filename 52

  14. Great code and works! One thing though that may be tripping people upwardly. The string Filename also is function of the Workbooks.Open Filename:= command. Anyone having issues with the Filename variable should try attempting to rename the cord to something else to avoid problems. Least what I had to do for this to properly work for me.

    • I am having filename problems

  15. fabulous!!! works amazing

  16. Filename = Dir(FolderPath & "*.xls*")

  17. Sorry not working for me, gives me error on file dir.

  18. Cant combine multiple files…no error shown,can we ship you lot the sample files-please share your email id

  19. Worked for me. Thank you then Much! saved me much fourth dimension for a work project. Thanks so much for uploading

  20. Like then many in this thread, this did non work for me – suggest it is taken down as a waste of valuable time!

  21. That worked astonishing! Thank you, thank you, thanks!

  22. Heaps thanks worked just perfect.. yous saved my time.

  23. Hi , how can I combine the first worksheet of multiple Excel workbooks in a specified binder into a single Excel workbook ?

  24. I have used this code but its is creating multiple tabs for the sheet with same name like Proper noun (i); Name(2); Name(three)

    Whatsoever solution

  25. I am getting Run-time error Bad file name or number
    I have used the path "H:Sheets"
    Please advise

    • Delete the :

  26. it didnt work, this came dorsum as an fault
    Filename = Dir(FolderPath & "*.xls*")

    • take you solve this instance ? I have same error …

    • try putting "" at the end of folder path (note that in dos, between file name and the directory there has to be "" sign

  27. I want to combine individual timesheets into i chief file, but the t/southward are protected. Volition I still be able to enter data and relieve it?

  28. I am getting debug in filename. Please help me

    • Remove the Environ("userprofile") & from your code. This just tells VBA to get to folders nether your user. But if yous already have something like "C:UsersVarunDocuments" in the folder path, this will throw the fault you become.

  29. Fantabulous !!!

    Is it possible to proper name the sheets accordingly to their filenames?

    • Howdy, i am getting debug in Filename.
      Can you help me ?

  30. Hie I have many workbook and i want to combine every second sheet from workbook together

  31. Cheers for this tip Sumit………….

    Merely information technology create multiple sub sheets of multiple files…what should to practise if we need all multiple files into only 1 unmarried sub sail and i also want that the information re-create should be in sequence

  32. Non working for me. I followed all the steps mentioned above.

    • juse WPS office

    • distressing i merely want to say. Utilize WPS Office

  33. error 1004:
    method 're-create' of object ' _worksheet' failed

    Debug =

    Sheet.Copy Later:=ThisWorkbook.Sheets(one)

    • Same upshot with me.

  34. non working this coding,
    error on
    Filename = Dir(FolderPath & "*.xls*")
    kindly slove this problem

    • I am getting the same fault

    • I used " " and entered folder path where the file is located inside the quotation and information technology worked for me.

  35. Howdy I demand some help in developing an extinction tool. Where 80 Pct can be done through formulas. Bit I need some help on remaining things. Will appreciate your help.

  36. not working 🙂
    need smart support

    • I am making an interpretation tool. The first method worked, simply that's not dynamic.
      I want the list to refresh if a new file is added to the binder.

  37. Thanx a lot !!!!! Saved lot of fourth dimension

  38. Super!!!
    Saved lot of time

  39. hello can i update daily log sheets / material employ canvas, warehouse sheet and combining into 1 for KPI ? daily folks relieve in same folder with different engagement and shift

  40. How-do-you-do Sumit
    It does'nt work for me after peressing the F5 I got ERE "BAD FILE NAME"
    As I meet numer of people hither have had the same ERE plz explicate the reason for information technology

  41. What about copying different sheets from different excel files to 1 sail by adding the new data to the erstwhile one ?

  42. Thank you so much for this VBA lawmaking! Excellent time-saver!

  43. Hi, I have to re-create 4 sheets from different excel files in one excel.
    Excel name in which i have to copy my information= Summary

    Data re-create from below listed excels
    Excel ane= Los Angeles CCAP ASE Job Log Backup 15-eleven-18
    Excel 2= Anaheim CCAP ASE Task Log Backup fifteen-11-xviii
    Excel three= BAY-North CCAP ASE Job Log Backup 15-eleven-18
    Excel 4= San Diego CCAP ASE Job Log Backup 15-xi-18

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

    Please aid I take to submit a written report to my manager.

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

    • Did you lot always find a solution? I am in a similar boat.

  45. how to combine three workbook in one workbook easy and quick

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

  47. I am getting an fault equally bad file name,can someone help me with this error,would exist actually thankful!

  48. It doesn't work for me, I take "bad file name or number". do you know how to prepare information technology?

  49. helpful

  50. very useful ,I similar it very much !

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

  52. Attempting with CSV files. =) volition allow you know.

  53. I have a query . Suppose I accept to merge 652 workbooks into one. The filenames are 1.xlsx to 652.xlsx and i desire to merge them in ascending order of their filenames merely . Will this code merge information technology according to their sorted name in the binder or not ??

  54. Hi Sumit, need your help with the above macro.

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

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

    1. There are around 4000 employees and the workbook volition be each employee lawmaking wise
    2. There are 18 folders with workbooks with 1 worksheet for each employee.. there is a possibility that for an employee i of the workbooks will not be at that place
    3. The macro should create a workbook with the emp code then across the xviii folders should check whether workbook is available for the employee ID and if yep
    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 in a higher place macro is:

    1. How practice 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 assistance. I tin communicate on your official email id as well and volition exist happy if y'all tin provide a solution at the earliest even if it is chargeable.

    Regards

  55. That's bully thanks. It would exist even better if it named the sheets later the original file names.

    • yes

  56. Still usable. You need to place a "/" later your path if you re-create it in.

    • Thank you, this worked along with removing the Environ("userprofile") & code segment for FolderPath where I was able to combine 13 single sheet Excel files that I placed in a test folder on a mapped network drive (example: FolderPath = "Z:20198 – AUGMeeting AttendeesTest/"

  57. The code is actually work for me using in Excel 2016 after few experiments.
    Some tips for those who are struggling about getting error message:
    1) The lawmaking is worked for both xls and xlsx, don't need to modify information technology.
    two) The code ONLY works for worksheet, exercise endeavor to motility chart, you will get mistake. (anyone know how to move all together?)
    3) Endeavour i or 2 workbooks first to see whether information technology is piece of work for you or not.

    Hope these assist you all 🙂

  58. Wow first one actually worked:)

  59. This did not work. Yes it combines 2 workbooks, only it destroys the functionality of the formulas in the procedure. At present, 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" letters (sub 'utilize lists' with various other names). And regarding other formulas being destroyed, example: a simple ='TAB'!A1&" "&'TAB'!A2 type of formula is now swollen with file directory info. And so information technology reads like:
    ='C:UserEtcpathTAB'!A1&" "&'C:UserEtcpathTAB'!A2
    That last one I tin 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'thousand at a loss.

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

  60. Sumit Bansal yous Rock <3

  61. Thanks for posting this! I'm getting "Run-time mistake '52': Bad file name or number" – anyone know how I tin overcome this?

    • change .xls to .xl
      I removed the "environment" as well

  62. Demand multiple files into 1 unmarried canvass.

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

    How can I change this script to combined Sheets with the name "Sheet1" from multiple file into one file.

    • then how did you ended up making information technology work???

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

    • Ugh…me too!!!

  65. also, tin I just combine page ane of the workbooks to be combined into a unmarried workbook?

  66. If my files are on a network drive, practise I remove the "Environ" part of the FolderPath?

  67. It'due south not working for me..!! When i am pressing F5…no combination of sheets..>!!

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

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

      • is there any fashion if the multiple files tin exist consolidated into i single tab instead of multiple tabs?

      • Hi Sumit,
        Also it is not working for me for "xlsx" file. It is showing debugg at Filename = Dir(FolderPath & "*.xlsx*"). Delight get a solution. Cheers

        • I am getting the same error…..

          • Here When the Path In the Folder Is Copied Information technology Must Start Form The "Desktop" & Not Every bit "C:"

            If Rectify This And then It Automatically Works When pressing "F5"

        • Hither 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

  69. Thanks for this tip Sumit………….

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

    Pls. assist.

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

      • Hello Sumit,

        Firstly Thanks for concern.

        I accept send an email to yous for further description on it. Pls. have a look.

        • Hello Sumit………..

          Pls. assist on this…………

Comments are closed.

Source: https://trumpexcel.com/combine-multiple-workbooks-one-excel-workbooks/

Posted by: nixharme1942.blogspot.com

0 Response to "How To Combine Multiple Excel Files Into One Worksheet Using Macro"

Post a Comment

Subscribe to: Post Comments (Atom)

Iklan Atas Artikel

Iklan Tengah Artikel 1

Iklan Tengah Artikel 2

Iklan Bawah Artikel