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 key and click on each worksheet that you want to copy. Once you are satisfied with the selection, release the key.
3. With all the worksheets selected, right-click on any selected sheet and click on Move or Copy.
4. In the Move or Copy dialog box:
- 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 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+ 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:
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
Filename = Dir()
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+ 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.
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.