How to Combine Multiple Worksheets into One Workbook?

Method 1 – Combine Multiple Worksheets Into One Workbook Using Power Query

Steps:

• Go to the Data tab from the ribbon.
• From Get & Transform Data, click on Get Data.

• Select From Other Sources under the Get Data drop-down menu
• Click on Blank Query from the From Other Sources drop-down menu.

• This will launch the Power Query Editor.
• Use the following formula in the formula bar of the Query Editor.

• Press the Enter key to continue. This will display all the table names in the whole workbook, along with any named ranges and/or connections that may be present.

Please keep in mind that the Power Query formulas are case-sensitive.

• In the Content header cell, click on the double-pointed arrow.
• Uncheck the box Use original column name as prefix.
• Click on the OK button to close the window.

• This will merge all the worksheets’ content into one single workbook.

• Close the Power Query, and you’ll see that all the data is now combined and shows up in one single workbook.

Method 2 – Merge Worksheets into One Workbook with the Consolidate Tool

Steps:

• Go to the Data tab on the ribbon.
• Click Consolidate under the Data Tools category.

• Select any of the functions you wish to utilize to condense your data from the Function box. In this case, we’ll use Sum.
• Pick the range on the first worksheet in the Reference option by clicking the Expand Dialog icon.
• Click the Add button.
• Repeat the previous two steps for all the ranges that we wish to consolidate.
• Check the Top row or Left column checkboxes under Use labels in.
• If you want the combined data to update automatically when the data source updates, check Create links to source data.
• Click OK.

If your main worksheet already has data, ensure there are blank rows and columns to accommodate the combined information.

• You can see the data of every worksheet is now combined into one single workbook.

Method 3 – Combine Multiple Worksheets Into One with the Move or Copy Feature

Steps:

• Select the workbooks that will be combined into one workbook.
• Right-click on the sheet tab, then pick Move or Copy from the menu that appears.

• When the Move or Copy dialog appears, select the workbook you want to move or copy worksheets into from the To book drop-down menu. We selected Book1.
• In the Before sheet box, choose MainSheet, check the Create a copy box, and then hit the OK button.

• This will combine move or copy multiple worksheets from two or many workbooks into one.

Method 4 – Excel VBA to Combine Multiple Worksheets Into One Workbook

Suppose all the different worksheet data are in Sheet1. We want to combine all those Sheet1 data from different workbooks into a single workbook.

Steps:

• Go to the Developer tab from the ribbon.
• From the Code category, click on Visual Basic to open the Visual Basic Editor. Or, press Alt + F11 to open the Visual Basic Editor.

• You can also right-click on your worksheet and go to View Code. This will also take you to Visual Basic Editor.

• In the editor, click on Module from the Insert drop-down menu bar.

• This will create a Module in your workbook.
• Copy and paste the VBA code shown below.

VBA Code:

Sub Copy_Worksheets()
Dim wrkbk As Workbook
Dim Str As String
Str = "Sheet1"
For Each wrkbk In Workbooks
If wrkbk.Name <> ThisWorkbook.Name Then
wrkbk.Worksheets(Str).Copy_
Before:= ThisWorkbook.Sheets(1)
End If
Next
Set wrkbk = Nothing
End Sub

• Run the code by clicking on the RubSub button or by pressing F5.

• This will combine all the Sheet1 data from different workbooks into one single workbook.

Download the Practice Workbook

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