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)
Similar Readings
- How to Combine Rows in Excel (6 Methods)
- Combine Sheets in Excel (6 Easiest Ways)
- How to Combine Columns into One List in Excel (4 Easy Ways)
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
Read More: How to Merge Two Excel Sheets Based on One Column (3 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.
With the merge column wise, is there a way you can do this without including the headers from the addition sheets? Just the header from the first sheet so it is just one large table and not separate tables?
Hi, sbellmore
This happens because of the .UsedRange method in the 13th line in the code.
Set Rng = Worksheets(Work_Sheets(i)).UsedRange
You can replace .UsedRange with .Range method to put one dataset or a particular portion of the dataset (eg without headers). For example, with our dataset, you can use the following.
Set Rng = Worksheets(Work_Sheets(i)).Range(“B4:C7”)
or,
Set Rng = Worksheets(Work_Sheets(i)).Range(“B4”).End(xlDown).End(xlToRight)
The downside of using such lines of code is, if you have any dataset without headers, it may ignore the part or whole dataset in some cases. As some of your sheets may not contain a dataset starting from cell B7. For automation, unfortunately, using .UsedRange is the most efficient method.
Hello,
Is there a way to exclude some tabs? I have 10 tabs active but only want to merge 8 of them
Hi, DIEGO.
Thank you for your concern. Yes, there is a way to exclude some tabs and merge only the tabs that you want. The generic code for this is:
Sub Merge_Multiple_Sheets()
Row_Or_Column = Int(InputBox(“Enter 1 to Merge the Sheets Row-wise.” + vbNewLine + vbNewLine + “OR” + vbNewLine + vbNewLine + “Enter 2 to Merge the Sheets Column-wise.”))
Merged_Sheets = InputBox(“Enter the Names of the Worksheets that You Want to Merge. Separate them by Commas.”)
Merged_Sheets = Split(Merged_Sheets, “,”)
Sheets.Add.Name = “Combined Sheet”
Dim Row_Index As Integer
Dim Column_Index As Integer
If Row_Or_Column = 1 Then
Column_Index = Worksheets(1).UsedRange.Cells(1, 1).Column
Row_Index = 0
For i = LBound(Merged_Sheets) To UBound(Merged_Sheets)
Set Rng = Worksheets(Merged_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 – 1
Next i
Application.CutCopyMode = False
ElseIf Row_Or_Column = 2 Then
Row_Index = Worksheets(1).UsedRange.Cells(1, 1).Row
Column_Index = 0
For i = LBound(Merged_Sheets) To UBound(Merged_Sheets)
Set Rng = Worksheets(Merged_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 If
End Sub
When you’ll run the code, it’ll ask for two inputs. Enter 1 if you want to merge the sheets row-wise, or 2 if you want to merge them column-wise.
And in the second input box, enter the name of the sheets that you want to merge. Don’t forget to put commas between the names, and don’t put any space after the commas.
Once you are done entering the inputs, click OK. You’ll find your selected tabs merged row-wise or column-wise in a new sheet called “Combined Sheet”.
Good luck.
Hello. Is there any reason it only merge data until the 4th sheet? I’ve been running it just fine until i reach the 5th sheet.
Hello ABC,
Thanks for your comment. No, there is no such limit to merging data. This code should perfectly work for the 5th sheet too. However, there are a limit for row (1,048,576) & column (16,384) numbers in Excel. If after merging the 5th sheet your data crosses this limit, it may not work.
Hope you have found your solution. If you face any further problems, please share your Excel file with us at [email protected].
Regards
Arin Islam,
Exceldemy.