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 4. Total comments: 249

  1. I am trying to combine 31 different excel workbooks and all of them have a standard format and multiple sheets. However, they also have their macros too. When I merge them the macro is not producing the same answer in the combined workbook as it did previously and macros are all messed up now. Is there anyway I can combine all of them with their individual macros that dont get mixed up?

  2. We created a template with header and column names, we decided to split the task across workers, gave each of them the designed template with same column names. Each returned excel files with partly filled data (this I due to division of labour on our on end, reason why we created the template) since these files have the same row names and volume, we would like to merge the various files into one and have excel merge this files.
    Take for instance worker A works on the first 20 and B works on 21-40 or in no definite pattern, would excel be able to merge these files together? Please I need a solution, thanks

  3. Is there a way to print this without trying to copy as it? I need to do this but know I won't remember all this. Brain works better at 7am. And which is the easiest method? Thank you.

    1. Hi!
      If I understood the problem correctly, in order to print this article, you can use the key combination Ctrl + P

  4. If anyone else runs into an error saying "Method 'Copy' of object '_Worksheet' failed", it could be because you there are worksheets among your target files that are hidden.

    Adding the following line below BEFORE the copy would fix it, but bear in mind it will also unhide all hidden sheets

    wksCurSheet.Visible = xlSheetVisible
    wksCurSheet.Copy after:=wbkCurBook.Sheets(wbkCurBook.Sheets.Count)

  5. Script works perfectly however, I need them in a single sheet line under line.
    For more explanation
    I saved bulk data from outlook wish to have them in long date format (Date & Time). I would like to have them line by line

  6. Hello,

    How can I use the above code for specific sheets from various excels.

    Thank you

  7. How do you link columns in an excel file (say CAT.xlsx) to columns in another excel file(say DAT.xlsx) columns using macros?

  8. This worked great for me! is there a way I can add a column with the workbook name to each row so I can keep the data differentiated afterwards?

  9. Hi! I have 10 employees working on the same 5 tabs, each assigned there own rows. Will this work to merge all the information into one file??

    1. Hello!
      Unfortunately, without seeing your data it is difficult to give you any advice.
      You can try using Combine Sheets.
      It is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and check how it works for free.
      If you have any other questions, please don’t hesitate to ask.

  10. Thanks for the script.

    This successfully merges the selected files in a single folder location. However, is there a way if the files to be merged are placed at different folder locations.

  11. How does one merge multiple files into one worksheet on Excel for Mac? When I try to run this macro I get the following:

    "Run-time error '1001':
    Method 'GetOpenFilename' of object '_Application' failed

    1. Same problem here. Doesn't work on Excel for Mac.

  12. hello. is it possible to merge multiple excel files into 1 excel file but with multiple sheet?

    1. Hello!
      If I understand your task correctly, our Ablebits Data - Copy Sheets - Selected Sheets to One Workbook may help you solve it in a few clicks. It is available as a part of our UltimaIt is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and check how it works for free.te Suite for Excel that you can install in a trial mode and check how it works for free.

  13. This is a great script! Works perfectly for me. . . . However, I need the new Tab names to be the File name that I merged. How can that be achieved?

    1. Hello!
      Unfortunately, this option is not available. In addition, changing the name of a worksheet can destroy formulas that referenced old worksheet names.

      1. thanks! that worked well for me. is it possible to just copy the first tab in each workbook?

  14. VBA can do everything you want

  15. This is so useful

  16. Hello,

    I want to merge two spread sheets but the information in each is different. If use your merge process will this program merge the sheets and keep the differences different?

  17. Hi,

    How do I merge / append multiple excel files into one single sheet - Eg. 5 files, which needs to be merged all together in one sheet, not a one excel file per sheet but all in one?

    thanks in advance

    /T

    1. Hello!
      To combine multiple files into one sheet, you can use Ablebits Data - Copy Sheets - Data from selected sheets to one sheet.
      It is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and check how it works for free.

  18. thanks for the vba macro - I over a hundred separate reports to merge into on and this definitely helped save some time :D

  19. Worked Great!!! Many Thanks!!!

  20. This was very helpful, thank you. Could you suggest an amend that would allow data to be pulled from encrypted Workbooks as well? (provided of course that you had all the individual file names and corresponding passwords to access those files?)

  21. 4 files needs to be merged into 1 file as 4 separate tabs. To be done for 2000 files with an outcome of 500 files with 4 tabs each. We cant don it manually, is there a VBA code to pick up files and merge them ? File naming convention can be devised based on the VBA code. Please suggest.

    1. Hello!
      I can't tell you anything about VBA code. But Ultimate Suite for Excel can combine tables.

      Could you please describe your task in more detail and send us a small sample workbook with the source data and expected result to support@ablebits.com? Please shorten your tables to 10-20 rows/columns and include the link to your blog comment.

      We'll look into your task and try to help.

  22. I have two excel files. One has name and variable A. The second has name and variable B. I would like to combine the two so that I can see name, variable A, and Variable B. How do I do that?

    1. Hello!
      The information presented to you is not enough to give you advice.
      If Name is a column for comparing your files, then you can of course combine them.
      Please describe your problem in more detail. It’ll help me understand it better and find a solution for you.

  23. Hi. thank you very much for the super handy trick!
    I have one question. may I know like is there any limit for this shortcut? for example it only works for certain number of excel sheets or certain rows or columns in each excel sheets

    1. Hello!
      Our add-in doesn't have clearly defined limitations on the number of sheets, rows and cells with the data to process. However, it may take some time for the add-in to handle thousands of rows and the processing speed also depends on such things as the formatting of your data, other processes running at the moment, available system resources, etc.

  24. This was awesome! Saved me so much time. Lifesaver.

  25. This is great! Could you please confirm if this is a one-time copy/paste (which move and copy does quite well, so must be more...) or if this is actually linked to the other spreadsheet? In other words, will my merged workbook now get updated automatically when the other workbook is updated or changed? Which is what we want. Fingers crossed!

  26. Can anyone advise how to use this code and also retain the name of the excel in individual sheets? Now when I used this code, it did work out but naming convention is sheet1(x).
    I want the name as per the names of the excel sheets.

  27. Hi,
    I'm using this VBA to merge multiple workbooks but holding down the control key doesn't allow me to select multiple workbooks. Neither does holding down Shift. Any ideas what's wrong?
    Thanks in advance.
    Brendan

  28. I got an error.
    When I click on "debug", the following line of code was highlighted in yellow:

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

    It happened both with the excel file that I downloaded and with the one I created myself.

    Help?

  29. I have Excel 16.38 and can't seem to figure out how to get the workbook tabs at the top to appear. I can see the worksheet tabs at the bottom but not the workbooks. How do I make them appear? It would seem fairly simple.

    Thank you

  30. Just wanted to say thanks for that macro, it saved me a lot of work.

  31. Hi! I am trying to modify this VBA code to let me pull only the first tab from multiple excel workbooks into a single excel file. Does anyone know what I should modify from above to make this work? Thanks in advance!

  32. I'm trying to merge 388 .xls into one. Each .xls has 1 tab. When I try and merge, it only brings in about 60%~ of the tabs. Thoughts?

    Thanks for the assistance!
    Sam

  33. this is a great example of a "jab jab jab, right hook" @garyV

  34. Merging multiple worksheets in to one worksheet is perfectly working fine. but with a small requirement, while merging data, how can we put the original file name (from which the data picked from) in the side by column, this is really very useful while migrating data, Can anybody look in to this and kindly advice?

    1. data1 data2 data3 from original file name
      cvcdfds sddfsdf dfssg file1
      vcxvxv dsfvsds dfssdg file2
      klmvlxkvv kmflk kllm;l file3

      how can we merge the data like this.
      can anybody help in this?

  35. This is great, how would you modify the code so the tab name is changed to be the source file name?

  36. Hi Sir

    We have Created Excel in one consolidate sheet and all party sheet in same excel. I need Link all the sheets link in consolidate what can ido

  37. Hello, I need your help in something...

    I have been working on an excel file for sometime, then I asked a friend to help me with a VBA code that would open several hyperlinks (word documents) that i selected, copy them and past them for me in one single word document. He couldn't do it but he asked someone for help... but what that other persone did is he made a new copy of my work and deleted all the sheets and worked on the sheet that i needed this fucntion... now it is hard for me to combine these 2 excels, or to copy that guys VBA code to my original excel file...
    in the new VBA code (function) he has a new command/button that does everything...

    thank you so much for your help

    1. So i found the solution for my problem, thx anyways

  38. VBA Macro for merging worked great! Thank you!

  39. Hi,
    How do I merge multiple sheets into one sheet using column name as the column order (example A, AN , B) does not match for all the sheets? Thank you for your kind help.
    Warmest Regards.

    1. Hello Victor!
      There are several ways to merge multiple tables or sheets. You can find out more about them on our blog following this link.

      However, there is a ready-made solution for your task in Ultimate Suite for Excel. Please have a closer look at the Merge Two Tables and Combine Sheets tools.

      You can install Ultimate Suite in a trial mode and test the tools for 30 days for free: https://www.ablebits.com/files/get.php?addin=xl-suite&f=free-trial
      I hope this information will be helpful for you.

  40. Worked a treat
    Saved a lot of monkeying about
    Thx

  41. I can use the script but I need the file name as the name of the imported sheet and not the sheetname.
    with regards,
    Patrick

  42. Great code. worked beautifully for me. As someone mentioned, a description of what the functions do would be very helpful.
    All in all thanks for the effort

    In my case, I had to combine csv files. I added .csv extension in the file filer and that pulls in CSV files as well
    FileFilter:="Microsoft Excel Workbooks And text Files (*.xls;*.xlsx;*.xlsm;*.csv)

  43. Your macro ran great.
    After it runs and pulls all my workbooks together, I have a lot of empty tabs in my master workbook.
    Do you have any ideas on what would be causing this?

  44. Hi, is it possible to add each sheet name into the consolidated Sheet?

    1. Hello Bindu,

      The current version of Combine Sheets has no option to insert the tables’ names in the resulting table. Our developers will check out this suggestion and try to implement it in one of the future versions, but I cannot give you the exact timing yet.

      However, there is a workaround I may recommend you. Add an additional column to each of the tables you are to combine (let’s call it Sheet_Name, for example). Note! This column should be named the same in each sheet.
      Then enter the following formula in this column to get the sheet’s name there:

      =MID(CELL("filename",A1),SEARCH("]",CELL("filename",A1))+1,255)

      This column will be added to the resulting table too and you’ll define the original data location by that.

  45. Trying to run this macro on Excel 2013 and get error message "Run-time error '1004': Excel cannot insert the sheets into the destination workbook, because it contains fewer rows and columns than the source workbook...."

    Is there a solution to this? None of the source files are open.

  46. thank you so much....

  47. Hi,
    i am using below macro but i need to copy only first sheet. please confirm
    ----------------------------------------------------------------------------------------------
    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

  48. Hi, Is it possible to combine data from two workbooks only when,

    In 1st workbook, I have Sheet1 & Sheet 2 data,
    &
    in 2nd workbook, I have same sheet1 and sheet 2 data,
    Required result: When I combine 1 & 2 worksheets, A data should get an update in A sheet and B data should get an update in the B sheet itself.

  49. I'm now the hero of my office thanks to your code... thank you!!

  50. I have 30 excels date wise data and I want to combine it into single excel. Please help.

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