Comments on: How to merge multiple Excel files into one

Merging multiple Excel workbooks into one file could be a cumbersome and long process. So, which is the best way to handle the problem: copy the sheet tabs, run VBA code or use a special tool? Continue reading →

Comments page 7. Total comments: 249

  1. Hi
    Thanks for the code.
    It would be possible to copy in each new sheet on the last column the filename.sheet_name of the original sheet/file ?
    Would help alot.

  2. Hi, I have 3 worksheets, all worksheets have 5 tabs with the same name, eg: Store_count, Store_size, State, Store_code, Store_type.
    I want to merge all the worksheets into one, by updating the all the worksheets to the first worksheet by row. The final file should consist of the same 5 tabs with complied rows.
    Can you give me the VBA code please?
    Thanks

  3. Thanks

  4. Hi
    I hade a multiple xls all with 4 particular named sheets. I need to merge all xls in to one and it must contains all those 4 sheets.

    Thanks

  5. Hi,
    could you please help me to export the data from a particular sheet to another workbook , and also it is possible to save as the source file and again edit the data and do this every time i want .
    i should tell you that i want to have a copy of my data from a excel file with a particular sheet in another sheet and i want to export the all file in to that excel file.

    Thanks

  6. Hi Ma'am,

    Can it also be used to combine CSV file?

  7. Thanks a lot it really helps me to combine all the excel files into single file but each file into different sheets, using the macro VBA code from Alex. Would be great, if the code can be altered to add all the sheets in to single one. (May be you might have another VBA code to combine all the sheets in to one.) Thank You very much for the much helpful article, which gets my job done in less than an hour.

  8. I have multiple department files that are macro enabled and locked cells. The first sheet is list of KPIs and rest all are individual KPI descriptive & calcs. I want to create a master file that has sheet 1 of all different departments and should be updated once the individual file data is changed. Is it possible. The options given doesn't combine macro enabled workbooks.

  9. Hi,
    I have multiple excel workbooks where the sheet1 will be having datas.
    In all the workbooks the heading for the data in the sheet1 is same.
    I want to merge all these multiple sheet1 to one single sheet.
    is it possible by macros

  10. AMAZING CODE!!! WOW! THANKS!

    ANY CHANCE YOU CAN ELABORATE ON SELECTING ONLY SPECIFIC SHEETS (ALL EXCEL FILES HAVE TABS WITH SAME NAME) TO BE MERGED?
    i.e. the files have 10 sheets, I only want to copy Sheet 1 and Sheet 2 from all files selected...thanks!!

    Thanks again! Cristina

  11. need help i want to create multiple excel file from one excel data file.

    it's a huge data and i need to create multiple excel file according employee.

  12. How can I keep data validation when I run this macro?
    The sheets that I am merging have drop downs, and I'm losing the data validation for this.

  13. This is great! Thanks for the Macros! It works well. But I must learn how to write it myself also. Thanks again!

  14. i have number of excel sheet and my manager wants to all the excel sheet on one excel sheet or other same option to look at everything with day to day update in number of excel sheet by employee if it possible ? if not with excel then what is the other option ?

  15. Thanks. Macro run very well and very fast.

  16. Hi Dear Friend,
    Thanks a lot

  17. Hi,
    The code is awesome. What will I have to add to the code or tweak it so that only a cell range from each worksheet is copied to my new Workbook?
    Thanks in advance for help!

    Regards,
    Gulmira

  18. Hi Svetlana,

    Your above mentioned VBA script works flawlessly!!!
    Could you please extend support for the following via same VBA.

    From multiple source files i need only Sheet 1 into one work book.

    Regards,

    Sohail

  19. The macro worked perfectly. Thank you. However, I noticed the file names are lost in the merging. In order words, the data is kept but the sheets are randomly named sheet 1 thur sheet x instead of retaining the file name. Is there a way to have the sheet titled as the file name?

  20. Hi, this works fine for over 500 worksheets, apart from the fact that i have an image and a diagram in each worksheet and these are not copying across, is there an addition to the Macro that would assist with this please?

  21. This errors out after 10 worksheets from 10 different workbooks if the worksheets all have the same name. Can this be fixed?

  22. Using sumif formula how to calculate total from two columns?

  23. Thank you so much for this. Could I possibly ask that the author breaks down the code to tell us how it functions. IN general for the code to copy over sheets into one workbook. It is great.

  24. the idea of combining workbooks into one is not just limited to worksheets, but also modules, userforms, thisworkbook, macros and the like... i'll worry about cleaning up once they are merged. But so far, i only know of a tool that combines sheets and leaves all the rest. Anyone know of a comprehensive book merge?

  25. I am trying to merge three Excel workbooks into one. I followed along with what the "How to Merge Excel Files With VBA" section said above about adding the macro. I am stuck on step 2 under "How to use the MergeExcelFiles macro". When I hit "Run", a message pops up saying "Compile Error Syntax Error" and in the code I entered, the "Sub MergeExcelFiles()" part is highlighted in yellow and has a yellow arrow pointing to it.

  26. I have 4 excel files on Sharepoint and want to create a summary report on 5 excel. is that possible? if yes then please share the process.

  27. Does anyone know how to compare two excel files merge them in VBA
    Basically i want to do the following
    Eg. File 1 firstname city
    Jack New york
    Joe Chicago
    File 2 firstname city
    Jack Boston
    Joe Chicago
    Jim San Diego

    Merged File firstname city
    Jack Boston
    Joe Chicago
    Jim San Diego

    Basically update and append
    Please let me know if anyone has done it or can do it

  28. I was able to use the code, it is very helpful. Thank you for this.

  29. Dave's code was extremely helpful, thank you!

  30. Hi! I'm trying to use the VBA but when I run it, it says Compile error: Invalid outside procedure.
    .. Do I need to change/ update something in the code to assign my directory etc?
    Thanks

    1. Lisa:
      Sounds as if there is some code outside the SUB at the top of the routine and the END SUB at the bottom. Check and see if this is the case.

  31. Thanks very much!

  32. hello.
    i need to combine multiple excel files into one excel sheet. is there any code.?

  33. Hi, Great Post for Merging Multiple Excel Files into One. But i have a Doubt everyone not able to write VBA Script. so i Think synkronizer Excel Sheet Tool is useful for compare, merge your excel sheet files without any coding. thanks

  34. 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?

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

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

  37. 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'

    1. 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!

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

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

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

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

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

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

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

  45. 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)

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

  46. I love you. The macro worked PERFECTLY!

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

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

  48. 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)

    1. Hi Hari,

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

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

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

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

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

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

      1. I could combine the files, but the images/logos were missing in the excel.There is this messages, images cant be diplaed

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 :)