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

• Close the Visual Basic window.
• Under the Developer tab, select Macros.

• The Macro dialog box will pop out.
• Select CombineFiles in the Macro name.
• Press Run.

• A Browse window will pop out.
• Select the files you want to combine and press OK.

• The sheets have been combined into a single workbook.

Download Practice Workbook

<< Go Back To Merge Excel File | Merge in Excel | Learn Excel