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

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

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

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

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

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

  6. thank you so much....

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

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

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

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

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

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

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

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

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

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

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

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

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

  20. THANKS FOR VBA

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

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

  23. Thanks

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

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

  26. Hi Ma'am,

    Can it also be used to combine CSV file?

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

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

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

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

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

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

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

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

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

  36. Hi Dear Friend,
    Thanks a lot

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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