Table of Contents
Merge Excel Files
To merge Excel files into one use the ‘Move or Copy’ feature. If you need to merge many Excel files consider using VBA to automate the process.
Move or Copy
To merge only a few Excel files (2-10) execute the following steps.
1. Open the Excel files you want to merge. For example quarter-1.xlsx and quarter-2.xlsx.
2. Go to the Excel file quarter-2.xlsx hold Ctrl and click the sheet tabs you want to move.
3. Right-click one of the selected tabs and then click Move or Copy.
The ‘Move or Copy’ dialog box appears.
4. Select quarter-1.xlsx from the “To book” drop-down list.
5. Click (move to end).
6. Check Create a copy.
7. Click OK.
8. Repeat these steps for any additional Excel files such as quarter-3.xlsx and quarter-4.xlsx.
9. Finally save the merged workbook as a new file by selecting File > Save As. As a result the original ‘quarter-1.xlsx’ will remain unchanged.
The merge is complete.
Tip: when merging Excel files you can quickly select multiple sheets by clicking the first tab holding down Shift and clicking the last tab.
Use VBA to Merge Files
To efficiently merge many Excel files use the VBA macro you can find here:
/vba/examples/import-sheets
1. Start by downloading the file ‘import-sheets.xlsm’ from the above link.
2. Move all the Excel files you want to merge into the folder located at ‘C:test’ on your computer.
3. Open ‘import-sheets.xlsm’ and click the ‘Import Sheets’ button to execute the macro.
This will combine all the Excel files into one. For instance in our demonstration we merged ‘book4.xlsx’ which contains a single sheet named ‘OneSheet’ and ‘book5.xlsx’ which includes two sheets ‘FirstSheet’ and ‘SecondSheet’. Of course the VBA script also works efficiently with numerous Excel files; a simple click of the button makes the merging process straightforward and efficient.
