Combine Multiple Excel Files into One Workbook with Separate Sheets (4 Methods)

In this example there are three different Excel Files and each of them contain a different Sheet.

Method 1 – Apply Move or Copy Operation to Combine Multiple Excel Files into One Workbook with Separate Sheets

STEPS:

Open the second Excel file (Combine Excel files 2).
Select the sheet (Sheet2) and right-click on the mouse.
Select Move or Copy in the menu.

The Move or Copy dialog box will pop out.
Select Combine Excel files.xlsx from To book options and select (move to end) in the field Before sheet.
Press OK.

Open the third Excel file (Combine Excel files 3).
Select sheet (Sheet3) and right-click on the mouse.
Select Move or Copy in the menu.

In the To book field, select Combine Excel files.xlsx, and in the Before sheet, select (move to end).
Press OK.

The separate sheets are combined in a single workbook.

Method 2 – Combine Multiple Excel Files Into One Workbook with Paste Link Feature

STEPS:

Copy cell B2 from Sheet2 in the Combine Excel files 2.

Go to the destination workbook. In this example, the destination is Combine Excel files.
Select a cell, B2 here.
Select Paste Link from the Paste Options.

A formula will be created as below.

Remove all the ‘$’ signs present in the formula and use the AutoFill tool to complete the series.
The source worksheet is returned as below.

Repeat the steps for the third Excel file.
The sheets have been combined in a single workbook.

Method 3 – Use Power Query to Combine Multiple Files into One Workbook with Separate Sheets

STEPS:

Open the first workbook (Combine Excel files).
Go to Data ➤ Get Data ➤ From File ➤ From Workbook.

The Import Data window will pop out.
Select Combine Excel files 2 and press Import.

The Navigator window will pop out.
Press Load.

Sheet2 from the second workbook is added as a Table.

Repeat the process to get Sheet3 from the third workbook.
All three sheets are combined in a single workbook.

Method 4 – Excel VBA to Combine Multiple Files into One Workbook with Separate Sheets

STEPS:

Open the destination workbook. (Combine Excel files).
Select Visual Basic from the Developer tab.

Select Module in the Insert tab.

The Module window will pop out.
Insert the following code.

Sub CombineFiles()
Dim FilesSelected, i As Integer
Dim tempFile As FileDialog
Dim MainBook, sourceBook As Workbook
Dim Sheet As Worksheet
Set MainBook = Application.ActiveWorkbook
Set tempFile = Application.FileDialog(msoFileDialogFilePicker)
tempFile.AllowMultiSelect = True
FilesSelected = tempFile.Show
For i = 1 To tempFile.SelectedItems.Count
Workbooks.Open tempFile.SelectedItems(i)
Set sourceBook = ActiveWorkbook
For Each Sheet In sourceBook.Worksheets
    Sheet.Copy after:=MainBook.Sheets(MainBook.Worksheets.Count)
Next Sheet
    sourceBook.Close
Next i
End Sub