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

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 (.exe file)

Table of contents

234 comments

  1. Amazing! thank you so much. the VBA code works smoothly

  2. VBA script combines data in different sheets but I need to combine multiple workbook with single sheet data into single sheet. I wanted try to combine all sheets into through Data Query, do we have convert all the data sheets into tables. Do we have any short cut for converting multple data sheets into tables.

  3. I have problems pressing ALT + F8 not working. FN + ALT F8 also not working

  4. That macro is amazing! I was even able to pull them off my OneDrive. Thank you so much!

  5. This Excel macro helped my greatly, thank you so much to the author!

  6. Hello when I run the macro only the first 2 files appear for me to select. There is a total of 6 files. Is this due to the size of the files and if so is there a way to fix it?

    Thank you!!

  7. Struggling with the fact that the sheets produced by this are not the original filenames. Is there a modification to the VBA macro code that I could make which would make the name of the sheet into the name of the file (minus the file extension)?

  8. I need to combine multiple files, and I need all to combine I seperate sheets.

  9. VBA script combines data in different sheets but I need to combine multiple workbook with single sheet data into single sheet. VBA script?

    • YOu might want to consider PowerQuery for this

  10. Hello,

    Is there any way to get data from specific cells from multiple spreadsheets and consolidate them in one spreadsheet?

    This is the situation: I want to consolidate 150 spreadsheets into one and I want to get the data in cell B4 and Cell J10 of every spreadsheet.

    Thank you

  11. Hello!

    I have nine .xls files in a folder, I get the following error when running the code:
    Run-time error '1004'
    That name is already taken. Try a different one.

    Clicking on debug takes me to this line:
    wksCurSheet.Copy After:=wbkCurBook.Sheets(wbkCurBook.Sheets.Count)

    A few months ago the code used to work, now it is not.
    The code runs OK always until the 3rd file. I only have 2 sheets per file. The code merges the first 4 sheets, naming the first 2 without the (number) and the next 2 sheets as "Sheetname (2)" and "Sheet (2)". Then on the beginning of the 5th sheet the error happens.

    All files to be processed are closed when running the code.

    Please help if you have the time. Thanks for the code.

    • I found the problem. The sheet names were all the same name and 27 characters long. I just renamed the sheets to something shorter and this macro worked.

  12. about to go crazy. how i can easily name worksheets' names with file names during mergin process? and how i can append this code part to the code below. please someone send me mail.

    respects.

  13. Change: <>
    To: <--this is what is causing the error

    Change: MsgBox "Procesed "
    to MsgBox "Processed " <-- this has not functional impact

    to end (before "End Sub") add
    Else
    MsgBox "No files selected", Title:="Merge Excel files"
    End If <-- this is to debug if you don't select file and it gives you a pop-up

    • Ha, please note the the "& l t % g t" is automatically converted to less than and greater than.... so use the "" instead of the & that gets translated in the comment submission

      If (vbBoolean <> VarType(fnameList)) Then

      less than and greater than in this statement, use the actual characters.

  14. Hi,

    I'm getting a syntax error on the first if statement - sorry, not too familiar with VBA - any advice?

    • Hi,

      I'm also getting a syntax error on the first if statement. I'm wondering if it could be from the settings.

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

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

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

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

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

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

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

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

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

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

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

      :)

  23. Thank you for the guidance.

    I went the VBA way and it worked as intended.

    Best Regards
    Ashish

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

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

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

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

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

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

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

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

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

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

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

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

  34. thanks,it worked just fine

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

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

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

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

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

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

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

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

  43. Hello,

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

    Thank you

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

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

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

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

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

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

    • Same problem here. Doesn't work on Excel for Mac.

  49. hello. is it possible to merge multiple excel files into 1 excel file but with multiple sheet?

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

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

    • Hello!
      Unfortunately, this option is not available. In addition, changing the name of a worksheet can destroy formulas that referenced old worksheet names.

      • thanks! that worked well for me. is it possible to just copy the first tab in each workbook?

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