When working with multiple Microsoft Excel Files, you may need to merge Excel Files and Sheets into a new or existing Excel file or merge multiple Excel files into one file. While you can always copy and paste data from one sheet to another or from one file to another, it can be tedious when there are too many of them. In this post, we will share how you can merge them using the built-in function.
How to Merge Excel Files & Sheets
Before you start merging files and sheets, plan it well. While you can always rearrange sheets later, the better you plan, the less amount of hours you will spend on sorting them after merging.
- Merge Sheets to a new or existing file
- Merge Multiple Excel Files
While the functions we will use here will allow you to move sheets, I recommend copying. You will have the choice if you ever need the original file again.
Merge Sheets to a new or existing file
Before merging, ensure you have all the Excel files open. Only when they are open can the Excel merge function choose a destination. You can also move sheets from multiple files into a new Excel file.
- Open the source Excel file, and switch to the sheet you wish to copy
- Click on Home tab > Cells section > Format > Move or copy sheet
- It will open a popup from where you can select existing Excel file or create a new file on the run
- When you choose a new file option, it will instantly create a new file but will not save it.
- When you choose an existing file, you have the option to select where the sheet will be inserted, i.e., before or after existing sheets or at the end of all sheets
- Make sure to select the checkbox— Create a copy. It will make sure your existing sheets remain in the sourcebook.
You can also move into an existing file, which is handy if there are too many sheets, and you need to insert the sheet in between them somewhere or move it to the end.
If you want to move multiple sheets to another Excel file, then before using “Move or copy sheet,” select the sheets using Ctrl or Shift. While Shift will allow you to select adjacent sheets or a range of sheets, Ctrl will let you choose the individual sheets. The rest of the steps are the same. You can use this to manually merge Excel files.
Merge Multiple Excel Files
Merging Excel files can be tricky, so we will use a VBA code from ExtendOffice. It allows you to combine multiple files or workbooks in a folder.
- Create a new Excel spreadsheet, and press ALT + F11 to open Developer section
- Click on Insert menu, and then Module
- Paste the code mentioned below. Name the Module as MergeExcel
Sub MergeExcel()
Path = "D:\ExcelFiles\"
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 SubNext, press Alt + F8 to open the Macro dialog. It will reveal all the Macros in the sheet. Select MergeExcel and click Run. You will be prompted to save files; make sure to do so. Once you have done so, all the sheets from different Excel files will become available in the Excel file where you ran the Macro. The ExtendOffice.com Website has many such macros.
That said, it will not be a smooth experience on the first try. Stay assured that it will not delete any files, but I suggest keeping a backup of the files. Experiment with some random data and check if you are getting the desired result using the macro.
We hope the tutorial was easy to understand and that you can merge Excel Files and Sheets.
