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

In this article, I’ll show you how you can merge data sets from multiple sheets into one sheet with VBA in Excel.

How to Merge Multiple Sheets into One Sheet with VBA in Excel: 2 Easy Ways

Here 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.

Our objective today is to merge the data sets from these multiple worksheets into one worksheet with Visual Basic Application (VBA).

1. Merge Data Sets from Multiple Sheets into One Sheet with VBA Row-wise

First, we’ll merge or combine data from multiple sheets into one sheet with VBA row-wise.

You can use the following VBA code for this purpose.

⧭ 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:

• Here, 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”

• I’ve put 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

2. Merge Data Sets from Multiple Sheets into One Sheet with VBA Column-wise

Now we’ll again merge the data sets from multiple worksheets into one sheet, but this time we’ll merge them column-wise.

Use the following VBA code:

⧭ 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:

• Here also, 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”

• I’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. So, 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 Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Conclusion

Using these methods, you can merge data sets from multiple sheets into one sheet with VBA both row-wise and column-wise. Do you have any problems? Feel free to ask us.

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