How to merge multiple Excel files into one

The tutorial shows three ways to combine Excel files into one: by copying sheet tabs, running VBA, and using the Copy Worksheets tool.

It is a lot easier to process data in a single file instead of switching between numerous sources. However, merging multiple Excel workbooks into one file could be a cumbersome and long process, especially if the workbooks you need to combine contain multiple worksheets. So, how would you approach the problem? Will you be coping sheets manually or with VBA code? Or, do you use one of the specialized tools to merge Excel files? Below you will find a few good ways to handle this task.

Note. In this article, we are going to look at how to copy sheets from multiple Excel workbooks into one workbook. If you are looking for a quick way to copy data from several worksheets into one sheet, you will find the detailed guidance in another tutorial: How to merge multiple sheets into one.

How to merge two Excel files into one by copying sheets

If you have just a couple of Excel files to merge, you can copy or move sheets from one file to another manually. Hers's how:

  1. Open the workbooks you wish to combine.
  2. Select the worksheets in the source workbook that you want to copy to the main workbook.

    To select multiple sheets, use one of the following techniques:

    • To select 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.
    • To select non-adjacent sheets, hold the Ctrl key and click on each sheet tab individually.
  3. With all worksheets selected, right click on any of the selected tabs, and then click Move or Copy….
    Select the worksheets you want to merge.
  4. In the Move or Copy dialog box, do the following:
    • From the Move selected sheets to book drop-down list, select the target workbook into which you want to merge other files.
    • Specify where exactly the copied sheet tabs should be inserted. In our case, we choose the move to end option.
    • Select the Create a copy box if you want the original worksheets to remain in the source file.
    • Click OK to finish the merge process.

    Copy sheet tabs from one Excel file to another.

The screenshot below shows the result - sheets from two Excel files combined into one. To merge tabs from other Excel files, repeat the above steps for each workbook individually.
Sheets from two Excel files are merged into one.

When coping sheets manually, please be aware of the following limitation imposed by Excel: it is not possible to move or copy a group of sheets if any of those sheets contains a table. In this case, you will have to either convert a table to a range or use one of the following methods that do not have this limitation.

How to merge Excel files with VBA

If you have multiple Excel files that have to merged into one file, a faster way would be to automate the process with a VBA macro.

Below you will find the VBA code that copies all sheets from all Excel files that you select into one workbook. This MergeExcelFiles macro is written by Alex, one of our best Excel gurus.

Important note! The macro works with the following caveat - the files to be merged should not be open physically or in memory. In such a case, you will get a run-time error.
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

How to add this macro to your workbook

If you'd like to insert the macro in your own workbook, perform these usual steps:

  1. Press Alt + F11 to open the Visual Basic Editor.
  2. Right-click ThisWorkbook on the left pane and select Insert > Module from the context menu.
  3. In the window that appears (Code window), paste the above code.

For the detailed step-by-step instructions, please see How to insert and run VBA code in Excel.

Alternatively, you can download the macro in an Excel file, open it alongside your target workbook (enable macro if prompted), then switch to your own workbook and press Alt + F8 to run the macro. If you are new to using macros in Excel, please follow the detailed steps below.

How to use the MergeExcelFiles macro

Open the Excel file where you want to merge sheets from other workbooks and do the following:

  1. Press Alt + F8 to open the Macro dialog.
  2. Under Macro name, select MergeExcelFiles and click Run.
    Run the MergeExcelFiles macro.
  3. The standard explorer window will open, you select one or more workbooks you want to combine, and click Open. To select multiple files, hold down the Ctrl key while clicking the file names.

Depending on how many files you've selected, allow the macro a few seconds or minutes to process them. After the macro completes, it will notify you how many files have been processed and how many sheets have been merged:
The result of executing the MergeExcelFiles macro

Combine multiple Excel files into one with Ultimate Suite

If you are not very comfortable with VBA and looking for an easier and faster way to merge Excel files, have a look at the Copy Sheets tool, one of 60+ time saving features included with our Ultimate Suite for Excel.

With the Ultimate Suite, merging multiple Excel workbooks into one is as easy as one-two-three (literally, only 3 quick steps). You don't even have to open all of the workbooks you want to combine.

  1. With the master workbook open, go to the Ablebits Data tab > Merge group, and click Copy Sheets > Selected Sheets to one Workbook.
    Merge multiple workbooks into one.
  2. In the Copy Worksheets dialog window, select the files (and optionally worksheets) you want to merge and click Next.
    Select the worksheets you want to merge.

    Tips:

    • To select all sheets in a certain workbook, just put a tick in the box next to the workbook name, all the sheets within that Excel file will be selected automatically.
    • To merge sheets from closed workbooks, click the Add files… button and select as many workbooks as you want. This will add the selected files only to the Copy Worksheets window without opening them in Excel.
    • To copy only a specific area in a certain workbook, hover over the sheet name with your mouse, then click the Collapse Dialog icon Collapse Dialog icon and select the desired range. By default, all data is copied.
  3. Select one or more additional options, if needed, and click Copy. The screenshot below shows the default settings: Paste all (formulas and values) and Preserve formatting.
    Choose how you want to copy worksheets.

Allow the Copy Worksheets wizard a few seconds for processing and enjoy the result!
Sheets from multiple Excel workbooks are merged into one file.

To have a closer look at this and other merge tools for Excel, you are welcome to download an evaluation version of Ultimate Suite. If you like the tools and decide to get a license, don't miss this special opportunity for our blog readers:

Get Promo Code for Ultimate Suite - your special price!

Other ways to merge Excel sheets and combine data

The above examples have demonstrated the best techniques to merge multiple Excel files into one. For more ways to combine sheets in Excel, please check out the following resources.

Available downloads

Macro to merge multiple Excel files (.xlsm file)
Ultimate Suite 14-day fully-functional version (.zip file)

126 responses to "How to merge multiple Excel files into one"

  1. Dave says:

    The VBA Macro does not merge the data into one sheet. It only brings over sheets from other workbooks.

  2. Dave says:

    The VBA code will merge data into one from multiple workbooks.

  3. Dhroov Prasad says:

    Hi,
    Multiple sheets can be easily merge into one by a tool called as Power Query, its a separate tool needs to be download from Excel.
    Power query lets you to combine multiple sheets into one and helps you to compile the data.
    There is also a tool called as Power Pivot, which helps to apply Pivot on multiple sheets together.

  4. Harish Dutt says:

    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

  5. Wilhelm Otnes says:

    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

    • Robert Gonzalez says:

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

      • Tim says:

        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

  6. hari says:

    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)

  7. Jamshed Mulla says:

    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

    • החושב says:

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

  8. Jeaux says:

    I love you. The macro worked PERFECTLY!

  9. Fernando says:

    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)

    • Aksana (Ablebits.com Team) says:

      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.

  10. Ganesh says:

    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.

  11. Tim Overath says:

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

  12. Camie D says:

    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.

  13. Abdul says:

    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.

  14. Kirolos says:

    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

  15. Neal says:

    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

  16. Brenda Kennedy says:

    This worked beautifully! THANK YOU! Truly a lifesaver!

  17. Nan says:

    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'

  18. Nan says:

    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.

  19. Anita says:

    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

  20. Alberto Ratshivhombela says:

    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?

  21. Maan says:

    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

  22. sun says:

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

  23. Jenko says:

    Thanks very much!

  24. Lisa says:

    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

    • Doug says:

      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.

  25. Joshua says:

    Dave's code was extremely helpful, thank you!

  26. Jay says:

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

  27. Nikhil Varma says:

    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. prabhjeet SIngh says:

    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.

  29. Brooke says:

    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.

  30. pat says:

    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?

  31. John Donovan says:

    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.

  32. Manju says:

    Using sumif formula how to calculate total from two columns?

  33. Mike says:

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

  34. Neil says:

    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?

  35. Nadine says:

    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?

  36. Sohail says:

    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

  37. Gulmira says:

    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. Kamil Yucedal says:

    Hi Dear Friend,
    Thanks a lot

  39. Vk says:

    Thanks. Macro run very well and very fast.

  40. Sumit Patel says:

    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 ?

  41. Stephen says:

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

  42. VenkatP says:

    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.

  43. Abhay says:

    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.

  44. Cristina says:

    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

  45. Shinoj says:

    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

  46. Tasneem says:

    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.

  47. Jerome says:

    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. Joan says:

    Hi Ma'am,

    Can it also be used to combine CSV file?

  49. waris says:

    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

  50. veena says:

    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

  51. Devender Kashyap says:

    Thanks

  52. Toh says:

    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

  53. Valentin says:

    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.

  54. GOP says:

    THANKS FOR VBA

  55. Nick C says:

    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.

  56. Esther says:

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

  57. Munna says:

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

  58. Prasad says:

    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.

  59. A shah says:

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

  60. Paras says:

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

  61. Rohan Byanjankar says:

    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.

  62. Vishal Sinha says:

    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

  63. vinay says:

    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

  64. Maya says:

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

  65. Michele McP-Terry says:

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

  66. Bindu says:

    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.

  67. faizan haq says:

    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

  68. saravanakumar says:

    thank you so much....

  69. Mike says:

    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.

  70. Bindu says:

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

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

  71. Marty says:

    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?

  72. Dean Norris says:

    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)

  73. Patrick says:

    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

  74. Geoff says:

    Worked a treat
    Saved a lot of monkeying about
    Thx

  75. Victor Tan says:

    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.

  76. Valerie says:

    VBA Macro for merging worked great! Thank you!

  77. Emad.Sleimann says:

    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

  78. JAGAN says:

    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

  79. Simon Ramsden says:

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

  80. SURYA SEKHAR says:

    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?

    • SURYA SEKHAR says:

      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?

  81. tom ford says:

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

  82. Sam says:

    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

  83. Eddie says:

    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!

  84. Dallas says:

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

  85. Jeff says:

    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

  86. Kat says:

    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?

  87. Brendan White says:

    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

  88. Shreyas Vaidya says:

    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.

  89. Deb Warner says:

    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!

  90. RC says:

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

  91. Imran Saiful says:

    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

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

  92. liz says:

    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?

  93. RB says:

    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.

  94. Phil says:

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

  95. Tony G says:

    Worked Great!!! Many Thanks!!!

  96. allan says:

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

  97. Twinkle says:

    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

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 :)
Ultimate Suite for Microsoft Excel
Merge two tables
Combine Sheets
Merge Duplicates
Consolidate Sheets
Copy Sheets
Merge Cells
Vlookup Wizard