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 2. Total comments: 90

  1. Hello- Thank you for making this script. Each of my tabs from different files are the same name, resulting in "File", then "File (2)" "File (3)". The space in the tab name is not acceptable by any formulas I am utilizing. How do I remove the space from the script?

    1. Hi Jeffrey,

      Try adding a line that renames the copied sheet. This could look something like this:

      ...
      wksCurSheet.Copy after:=wbkCurBook.Sheets(wbkCurBook.Sheets.Count)
      ActiveSheet.Name = "File" & countFiles
      ...

  2. For example if there's sales data for Fruits like Mango, Apple, Banana, Kiwi in four worksheets in a workbook named Sales_March and there are similar workbooks for rest of the months with same 4 worksheets. Now how do i combine the yearly sales data for Mango in one sheet, Apple in one sheet etc for all the four fruits in a new workbook? I have been trying to figure out a direct method for this but couldn't find anything yet.

    1. Hello!
      We have a ready-made solution for your task. I recommend paying attention to the tool Combine Sheets. You can merge data from multiple sheets and select columns to merge. 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.

  3. How is it possible to merge two xcel spreadsheets keeping the columns that are in target and removing any rows from target that are no longer present in source.

    A b c d. ->. A b c.
    1. 1
    2. 3.
    3.

    Result

    A b c d
    1
    3

  4. I am trying to combine multiple excel sheets, however, I only need one tab from each sheet. The tab name is the same in all the sheets I am trying to combine. Is there a way to isolate and just pull the one tab from each excel sheet?

    1. Hello!
      I can recommend you a special tool for сopying selected worksheets from several workbooks to one file - Copy Sheets. You can combine all the sheets you need from different Excel files into one new workbook in a couple of clicks. 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.

  5. I am trying to merge spreadsheet B that has phone numbers, (total 175), on them, and match them to another spreadsheet A , that has 450 rows.
    SS B was returned to me by a company that provides phone numbers, but they could only provide 175 numbers out of the original 450 rows of data.
    Because I have more notes on Spreadsheet A, I would like to merge Spreadsheet B to SS A.
    How can I do this ??

  6. Can this macro be modified to include CSV files?

    1. Hi,

      Adding *.csv; to the list in the line below allows you to select and merge .csv files.

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

      Which becomes

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

      :)

  7. Hellow, how can I use macros if I want for example I have thirty people and they make transactions every week so if I click among of one person out of thirty people in Excell I want to bring me all his or her information concerning transactions that they made, so it is possible?

  8. Would it be possible to only merge specific workbooks in a folder? I have a folder with multiple files and need to pair them up. So instead of creating multiple folders, I was thinking of a table where Workbook A would pair up with Workbook B, C with D? can this be achieved?

  9. How can I use this macro if I want to copy over just 1 of the 4 sheets in each workbook? I'm having trouble successfully referencing the single sheet. Also, how can we alter this to paste values?

  10. your combine macro is the only one I could get to work for me.
    thanks

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    1. Hey that is an interesting idea - could you elaborate?

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

    1. Hi Dave,

      Exactly :) This whole article is about merging multiple Excel files into one, i.e. copying sheets from multiple workbooks into one workbook.

      If you are looking to merge data from multiple sheets into one sheet, you can try this VBA code or our Copy Sheets tool.

      1. Hi Svetlana,
        My requirement is quite similar to Dave's. So i went to the link provided by you above, But even that VBA code does not consolidate the data into one sheet, but only creates different sheets in the master sheet. Can you please help me?

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