How to merge Excel files into one

February 5, 2022

The tutorial explains how to merge Excel files by copying sheets or running a VBA code. It allows you to copy a single sheet or multiple worksheets from multiple Excel workbooks.

How to merge two Excel files into one by copying sheets

If you have just a few Excel files to merge, you can manually copy or move sheets from one file to another following a few simple steps:

1. Open all the files from which you want to combine sheets.

2. Select all the sheets from the source workbook that you want to move to the master workbook.

You can select a single sheet or a group of sheets. To select multiple sheets, press the CTRL key and click on each worksheet that you want to copy. Once you are satisfied with the selection, release the CTRL key.

3. With all the worksheets selected, right-click on any selected sheet and click on Move or Copy.

Select the Excel sheets that you want to merge.

4. In the Move or Copy dialog box:

Copy the selected sheet tabs from one Excel file to another.
  • From the Move selected sheets to book drop-down menu, select the target workbook where you want to copy the sheets.
  • Specify the position where the copied sheet tabs should be inserted. You can move them either to the end (i.e., after all the other worksheets) or before a specified sheet.
  • Check the Create a copy box if you want your source file to remain unchanged.
  • Click OK to finish merging data.
The sheets from the two Excel files have been combined.

The Excel sheets have been copied and inserted at the end of the file.

The same method can be used to combine multiple Excel files as well. However, you have to merge data from each Excel file at a time until you have gone through all the Excel files. Unfortunately, you can't merge multiple Excel files at once using this method. This is why the faster solution would be to use the VBA code described below, which allows you to simultaneously merge data from all opened Excel files.

How to merge Excel files with VBA

If you have many different Excel files that you want to merge, a more straightforward solution would be to use VBA code. This allows you to combine multiple sheets located in multiple files quickly. Here are the steps that you need to complete:

1. Create a new Excel file that will be used to import all the data.

2. Press ALT + F11 to open the Visual Basic Editor.

3. Right-click on the ThisWorkbook link from the left pane and select Insert > Module from the menu.

Insert a new VBA code module.

4. In the Code window, paste the following script:

Sub GetSheets()
Path = "C:\[PATH TO YOUR FILES]"
Filename = Dir(Path & "*.xlsx")
  Do While Filename <> ""
  Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
     For Each Sheet In ActiveWorkbook.Sheets
     Sheet.Copy After:=ThisWorkbook.Sheets(1)
  Next Sheet
     Workbooks(Filename).Close
     Filename = Dir()
  Loop
End Sub

This simple VBA macro has been first published on ExtendedOffice. Their Kutools for Excel utility pack also allows you to combine multiple files into one with just a few simple clicks.

Insert the VBA code to merge files

5. Change the folder path from the VBA code to match your source data. Make sure that your path ends with a backslash (\) like this:

Path = "C:\Users\Desktop\Merge Excel files\"

6. Save the Excel workbook as an XLSM file to enable macros.

7. From within the new Excel workbook, press ALT + F8 to open the list of available Macros.

8. Select your Macro from the list and click Run to process data. All the Excel sheets should now be copied to your master workbook.

Run the VBA code to merge multiple Excel files into one.

Note: All the files from which you want to import data need to be open.

If you are having difficulties merging multiple worksheets from different Excel files, please let me know, and I'll gladly help you.

About me
Radu from ExcelExplained

My name is Radu Meghes, and I'm the owner of excelexplained.com. Over the past 15+ years, I have been using Microsoft Excel in my day-to-day job. I’ve worked as an investment and business analyst, and Excel has always been my most powerful weapon. Its flexibility and complexity make it a highly demanded skill for finance employees. I launched excelexplained.com back in 2017, and it has become a trusted source for Excel tutorials for hundreds of thousands of people each year.

If you'd like to get in touch, you can contact me on LinkedIn.

Thousands of people have benefited from my free Excel lessons. My only question is, will you be next?

Essential Functions

Popular Articles

Excel courses

I have compiled a list of the best Excel online courses. It covers basic and advanced topics, VBA, and Power BI.
View courses
Excel is a very complex application, and understanding it on your own may prove difficult and time-consuming. I have created ExcelExplained.com to help you better understand each function and provide useful examples for different tasks. If you can’t find the answer to your question in one of the published articles, please leave a comment or send me an email, and I will do my best to help you out.

Affiliate Disclosure: When you purchase a product through a link on our website, I may earn an affiliate commission. There are no additional costs to you.
Copyright © 2017-2022 ExcelExplained.com
>