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.

=Excel.CurrentWorkbook ()

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