Merge Excel Files

Move or Copy | Use VBA to Merge 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:

https://www.excel-easy.com/vba/examples/import-sheets.html

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.