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.
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:
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.
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:
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
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.
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?
Please log in again. The login page will open in a new tab. After logging in you can close it and return to this page.