Rifat Hassan
In this article, I’ll show you how you can merge data sets from multiple sheets into one sheet with VBA in Excel.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
2 Ways to Merge Multiple Sheets into One Sheet with VBA
Here we have a workbook consisting of 3 worksheets, Sheet1, Sheet2, and Sheet3. Each contains the sales record of some products of 3 different weeks.
Sheet1 contains the sales record of Week 1.
Sheet2 contains the sales record of Week 2.
And 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 the data sets 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
Related Content: How to Merge Multiple Excel Files into One Sheet (4 Methods)Â
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 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
Related Content: How to Combine Sheets in Excel (6 Easiest Ways)
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.
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.
