How to Merge Multiple Sheets into One Sheet with VBA in Excel?

We have a workbook consisting of 3 worksheets, Sheet1, Sheet2, and Sheet3. Each contains the sales record of some products for 3 different weeks.

Sheet1 contains the sales record of Week 1.

Sheet2 contains the sales record of Week 2.

Sheet3 contains the sales record of Week 3.

We will merge this data into a single sheet.

Method 1 – Merge Data Sets from Multiple Sheets Into One Sheet with VBA Row-wise

⧭ VBA Code:

Sub Merge_Multiple_Sheets_Row_Wise()

Dim Work_Sheets() As String
ReDim Work_Sheets(Sheets.Count)

For i = 0 To Sheets.Count - 1
    Work_Sheets(i) = Sheets(i + 1).Name
Next i

Sheets.Add.Name = "Combined Sheet"

Dim Row_Index As Integer
Row_Index = Worksheets(1).UsedRange.Cells(1, 1).Row

Dim Column_Index As Integer
Column_Index = 0

For i = 0 To Sheets.Count - 2
    Set Rng = Worksheets(Work_Sheets(i)).UsedRange
Rng.Copy
    Worksheets("Combined Sheet").Cells(Row_Index, Column_Index + 1).PasteSpecial Paste:=xlPasteAllUsingSourceTheme
    Column_Index = Column_Index + Rng.Columns.Count + 1
Next i

Application.CutCopyMode = False

End Sub

⧭ Output:

Run this Macro (Merge_Multiple_Sheets_Row_Wise) and you’ll find the data sets from all the worksheets merged into a single worksheet called Combined_Sheet row-wise.

⧭ Notes:

The name of the merged worksheet is “Combined Sheet”. If you want to name it anything else, name it in the 7th and 15th lines of the code:

            Sheets.Add.Name = “Combined Sheet”

We’ve put a 1 column gap between each of the data sets in the combined sheet. If you want to change it, change it in the 16th line of the code:

Column_Index = Column_Index + Rng.Columns.Count + 1

Method 2 – Merge Data Sets from Multiple Sheets Into One Sheet with VBA Column-wise

⧭ VBA Code:

Sub Merge_Multiple_Sheets_Column_Wise()

Dim Work_Sheets() As String
ReDim Work_Sheets(Sheets.Count)

For i = 0 To Sheets.Count - 1
    Work_Sheets(i) = Sheets(i + 1).Name
Next i

Sheets.Add.Name = "Combined Sheet"

Dim Column_Index As Integer
Column_Index = Worksheets(1).UsedRange.Cells(1, 1).Column

Dim Row_Index As Integer
Row_Index = 0

For i = 0 To Sheets.Count - 2
    Set Rng = Worksheets(Work_Sheets(i)).UsedRange
    Rng.Copy
    Worksheets("Combined Sheet").Cells(Row_Index + 1, Column_Index).PasteSpecial Paste:=xlPasteAllUsingSourceTheme
    Row_Index = Row_Index + Rng.Rows.Count + 1
Next i

Application.CutCopyMode = False

End Sub

⧭ Output:

Run this Macro (Merge_Multiple_Sheets_Column_Wise) and you’ll find the data sets from all the worksheets merged into a single worksheet called Combined_Sheet column-wise.

⧭ Notes:

The name of the merged worksheet is “Combined Sheet”. If you want to name it anything else, name it in the 7th line and 15th line of the code:

            Sheets.Add.Name = “Combined Sheet”

We’ve put a 1-row gap between each of the data sets in the combined sheet. If you want to change it, change it in the 16th line of the code:

            Row_Index = Row_Index + Rng.Rows.Count + 1

Things to Remember

Each time you run any of these codes, Excel opens a new worksheet called “Combined Sheet” for you in the active workbook. If you already have a worksheet of the same name in your active workbook, rename it or delete it before running the code. Otherwise, you’ll get an error and the code won’t run.

Download the Practice Workbook

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