How to merge Excel files into one

July 10, 2024July 11, 2024

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.

Contents hide

1 How to merge two Excel files into one by copying sheets

2 How to merge Excel files with VBA

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

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.

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.