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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  26. thank you so much....

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

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

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

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

  31. Hi all,
    i need vba to merge multiple sheets data in one excel with same sheets [data should be merged accordingly with same sheets]
    Please assist
    Thank you

  32. Hi!
    This article was really helpful. But I am trying to do the exact same function for .xlsx in Libre Office in an Ubuntu environment, I am writing a python script using pandas and numpy.
    Is there any easier way with macros in Libre Office.
    Any help would be appreciated.
    Thank you

  33. Dear author, I want to combine specific excel sheets from multiple excel files. I want to do it with VBA as there are 100 + excel files. Please help me out.

  34. Thanks a lot for the Code. I really appreciate it.

  35. I want to combine data of different excel files into one file,kindly advise me

  36. Only merging 3 sheets and at 4th it is showing error, i checked with multiple sets of excel files, please help and is there any way to merge multiple excel files in to one sheet, the data format will be same.

  37. i need same names of each excel work book.
    and wroking slow, can he fast.

  38. Hi thanks for the code it saved me so much time.

  39. Hi Nadine,

    Were able to find out how to title the sheets with the originating file name? I am trying to do the same thing, but not sure how to get the code to do it.

  40. THANKS FOR VBA

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

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

  43. Thanks

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

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

  46. Hi Ma'am,

    Can it also be used to combine CSV file?

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

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

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

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

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