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

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

  2. I tried the macro and it looks like it combined all the files beautifully. However I have very random and numerous incidents where it changes the text formatting from 9 point to 18. There are too many to fix by hand. It happens on a cell and within a cell (only part of the text is resized larger). Microsoft 365 Apps for Enterprise V 2202.

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

  4. Hi Sir,

    I want to combine different excel files in a folder to one file in multiple sheets , i can do that but i have a challenge with excel files which i receive file name will be having date which changes so to combine all files without considering date i need to use prefix. Is there any solution where i can combine excel files from folder using prefix of file name . Example : Adverse_Events_26022022.xlsx and next time file name will be Adverse_Events_23032022.xlsx , if i take only Adverse_Events my macro will run. Could you please provide me solution.

    Thanks,
    Sujatha K

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

      :)

  6. Thank you for the guidance.

    I went the VBA way and it worked as intended.

    Best Regards
    Ashish

  7. I have 3 Macros and I want to merge all 3 macros together to make 1 macro. Ive merged but now everytime I run 1 macro it opens the old file. How can I fix?

  8. What can I do to make this process automatically? I mean, whatever change you do in the other sheets you can see these changes reflected in the combined data without doing the all process again and not duplicating data. Thank you!

  9. Can you share how to modify the VBA code to only consolidate visible tabs from the target files?

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

  11. You can use "Get Data" in the Data toolbar of Excel and import data from a folder, then choose combine and load. It's done quickly.

  12. More than 62 files, sheet name will not change to file name like sequence 1-62 of the first file.

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

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

  15. Instead of all the sheets in the file being combined, I just need a specific tab. I don't need all of the sheets in each file, I just need a specific tab. How can the VBA above be modified to help accomplish this?

    1. Hello!
      With the Copy Sheets tool, you can select multiple sheets and combine them in a workbook. Copy Sheets can also copy data from multiple sheets to one sheet. Read more here.

    2. Hi Jeremiah, I'm having this same issue, did you ever figure this out?

  16. I want to generate PDF files from an excel file. After this coding runs, the result is that I will have multiple PDF files with the suffix "-index.pdf" - (e.g., 875458-indexl.pdf) I can change the suffix to anything.

    And I will extract another set of PDF files from a zip file that have the identical file name as 875458.pdf.

    At the end, in one folder, there will be multiple pairs of files as below.

    875458.pdf
    875458-index.pdf
    875460.pdf
    875460-index.pdf
    954784.pdf
    954784-index.pdf
    My goal is to find a way to merge each pair of files into one file.

    The end result would be as below.

    875458.pdf
    875460.pdf
    954784.pdf

  17. thanks,it worked just fine

  18. Hello. Good day. Thanks for this. Would like to ask what could be added to this code to break the links of the files that you will merge? Because I'm getting #Value! in some cells that are linked to different sources. Thanks.

  19. Thanks for the macro. However, it changes colors such that original and output colors are different. Is there a fix? (I'm not knowledgeable in the whole macro code thing)

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

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

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

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

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

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

  26. Hello,

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

    Thank you

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

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

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

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

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

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

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

  34. VBA can do everything you want

  35. This is so useful

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

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

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

  39. Worked Great!!! Many Thanks!!!

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

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

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

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

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

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

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

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

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

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

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

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