Rifat Hassan
In this article, I’ll show you how to pull data from multiple worksheets into one worksheet with Visual Basic of Applications (VBA) in Excel.
Pull Data from Multiple Worksheets in Excel VBA (Quick View)
Sub Pull_Data_from_Multiple_WorkSheets_Horizontally()
Dim Sheet_Names() As Variant
Sheet_Names = Array("January", "February", "March")
Destination_Sheet = "Combined Sheet (Horizontally)"
Gap = 1
Set Destination_Cell = Worksheets(Sheet_Names(0)).UsedRange.Cells(1, 1)
Starting_Row = Destination_Cell.Row
Starting_Column = Destination_Cell.Column
For i = LBound(Sheet_Names) To UBound(Sheet_Names)
Worksheets(Sheet_Names(i)).Activate
Row_Width = ActiveSheet.UsedRange.Columns.Count
ActiveSheet.UsedRange.Copy
Worksheets(Destination_Sheet).Activate
ActiveSheet.Cells(Starting_Row, Starting_Column).PasteSpecial Paste:=xlPasteAll
Starting_Column = Starting_Column + Row_Width + Gap
Next i
Application.CutCopyMode = False
End Sub
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
3 Easy Methods to Pull Data from Multiple Worksheets in Excel VBA
Here I’ve got an Excel workbook that contains three worksheets titled January, February, and March respectively.
Each contains the sales record of some books for three different months in a bookstore.
So, without further delay, let’s continue with our main discussion. We’ll develop a Macro to pull data from these multiple sheets into one worksheet.
We’ll accomplish this in 3 ways possible.
1. Pull Data from Multiple Worksheets into One Worksheet Horizontally in Excel VBA
First of all, we’ll develop a Macro to pull data from the worksheets into one worksheet horizontally.
Open the new workbook and the worksheet where you want to merge these files.
Here I’ve opened a new worksheet called “Combined Sheet (Horizontally)”.
Then we have to enter the VBA code to develop the Macro. We’ll break the code step-by-step for better understanding and learning.
⧪ Step 1: Entering the Source Files and the Destination File
First of all, we’ve to enter the inputs into the code. These are the names of the sheets that we’ll combine (January, February, and March in this example), the name of the destination worksheet( Combined Sheet (Horizontally) here), and the gap between the data sets from different worksheets into the combined sheet (1 here, set it according to your need).
Dim Sheet_Names() As Variant
Sheet_Names = Array("January", "February", "March")
Destination_Sheet = "Combined Sheet (Horizontally)"
Gap = 1⧪ Step 2: Setting the Destination Cell, Starting Row, and Starting Column
Next, we’ll set the destination cell, starting row, and starting column of the combined data. The starting will be the same as the starting cell of the first worksheet.
Set Destination_Cell = Worksheets(Sheet_Names(0)).UsedRange.Cells(1, 1)
Starting_Row = Destination_Cell.Row
Starting_Column = Destination_Cell.Column⧪ Step 3: Iterating Through a for-Loop to Combine All the Sheets into One Worksheet
This is the most important step. We’ll iterate through a for-loop to combine all the sheets into one worksheet.
For i = LBound(Sheet_Names) To UBound(Sheet_Names)
Worksheets(Sheet_Names(i)).Activate
Row_Width = ActiveSheet.UsedRange.Columns.Count
ActiveSheet.UsedRange.Copy
Worksheets(Destination_Sheet).Activate
ActiveSheet.Cells(Starting_Row, Starting_Column).PasteSpecial Paste:=xlPasteAll
Starting_Column = Starting_Column + Row_Width + Gap
Next i⧪ Step 4 (Optional): Turning Off the CutCopyMode
Finally, we’ll turn off the CutCopyMode of VBA. You can skip this step if you want.
Application.CutCopyMode = FalseTherefore, the complete VBA code will be:
⧭ VBA Code:
Sub Pull_Data_from_Multiple_WorkSheets_Horizontally()
Dim Sheet_Names() As Variant
Sheet_Names = Array("January", "February", "March")
Destination_Sheet = "Combined Sheet (Horizontally)"
Gap = 1
Set Destination_Cell = Worksheets(Sheet_Names(0)).UsedRange.Cells(1, 1)
Starting_Row = Destination_Cell.Row
Starting_Column = Destination_Cell.Column
For i = LBound(Sheet_Names) To UBound(Sheet_Names)
Worksheets(Sheet_Names(i)).Activate
Row_Width = ActiveSheet.UsedRange.Columns.Count
ActiveSheet.UsedRange.Copy
Worksheets(Destination_Sheet).Activate
ActiveSheet.Cells(Starting_Row, Starting_Column).PasteSpecial Paste:=xlPasteAll
Starting_Column = Starting_Column + Row_Width + Gap
Next i
Application.CutCopyMode = False
End Sub
⧭ Output:
Run the code. It’ll pull data from all the input worksheets horizontally into the destination worksheet (Combined Sheet (Horizontally) here).
Read More: Excel VBA: Pull Data Automatically from a Website (2 Methods)
2. Pull Data from Multiple Worksheets into One Worksheet Vertically in Excel VBA
Now we’ll develop a Macro to pull data from multiple excel sheets into one worksheet vertically.
We’ll follow the same steps as mentioned in method 1.
First, insert the source worksheets (January, February, and March), the destination worksheet (a new worksheet called Combined Sheet (Vertically) here), and the gap (Again 1 here).
Then set the destination cell, starting row, and starting column.
And finally, combine the sheets with a for-loop.
The complete VBA code will be:
⧭ VBA Code:
Sub Pull_Data_from_Multiple_Worksheets_Vertically()
Dim Sheet_Names() As Variant
Sheet_Names = Array("January", "February", "March")
Destination_Sheet = "Combined Sheet (Vertically)"
Set Destination_Cell = Worksheets(Sheet_Names(0)).UsedRange.Cells(1, 1)
Starting_Row = Destination_Cell.Row
Starting_Column = Destination_Cell.Column
Gap = 1
For i = LBound(Sheet_Names) To UBound(Sheet_Names)
Worksheets(Sheet_Names(i)).Activate
Row_Height = ActiveSheet.UsedRange.Rows.Count
ActiveSheet.UsedRange.Copy
Worksheets(Destination_Sheet).Activate
ActiveSheet.Cells(Starting_Row, Starting_Column).PasteSpecial Paste:=xlPasteAll
Starting_Row = Starting_Row + Row_Height + Gap
Next i
Application.CutCopyMode = False
End Sub
⧭ Output:
Run the code. It’ll pull data from all the worksheets horizontally in the destination worksheet (Combined Sheet (Horizontally) here).
Read More: How to Pull Data From Another Sheet Based on Criteria in Excel
Similar Readings
- Convert Excel to Text File with Delimiter (2 Easy Approaches)
- How to Convert Notepad to Excel with Columns (5 Methods)
- Import Data into Excel from Web (with Quick Steps)
- How to Convert Excel to Text File with Pipe Delimiter (2 Ways)
- Transfer Data from One Excel Worksheet to Another Automatically with VLOOKUP
3. Pull Data from Multiple Worksheets into One Worksheet with an Operation in Excel VBA
We’ve learned to pull data from multiple worksheets both horizontally and vertically into one worksheet.
Now we’ll develop a Macro to pull data from these worksheets into one worksheet with a specific operation (Addition, Subtraction, Multiplication, or Division)
Again, we’ll follow the same steps as mentioned in methods 1 and 2.
First, insert the source worksheets (January, February, and March), the destination worksheet (a new worksheet called Combined Sheet (with Operation) here), the columns on which the operation will be implied (2 and 3 here), and the operation (Addition here).
Then set up the destination cell, starting row, and starting column.
And finally combining the files with a for-loop.
The complete VBA code will be:
⧭ VBA Code:
Sub Pull_Data_from_Multiple_WorkSheets_with_Operation()
Dim Sheet_Names() As Variant
Sheet_Names = Array("January", "February", "March")
Destination_Sheet = "Combined Sheet (with Operation)"
Dim Operation_Columns() As Variant
Operation_Columns = Array(2, 3)
Operation = "Addition"
Set Destination_Cell = Worksheets(Sheet_Names(0)).UsedRange.Cells(1, 1)
Starting_Row = Destination_Cell.Row
Starting_Column = Destination_Cell.Column
Worksheets(Sheet_Names(0)).Activate
ActiveSheet.UsedRange.Copy
Worksheets(Destination_Sheet).Activate
ActiveSheet.Cells(Starting_Row, Starting_Column).PasteSpecial Paste:=xlPasteAll
For i = LBound(Sheet_Names) + 1 To UBound(Sheet_Names)
Worksheets(Sheet_Names(i)).Activate
For j = LBound(Operation_Columns) To UBound(Operation_Columns)
ActiveSheet.UsedRange.Range(Cells(2, Operation_Columns(j)), Cells(ActiveSheet.UsedRange.Rows.Count, Operation_Columns(j))).Copy
Worksheets(Destination_Sheet).Activate
If Operation = "Addition" Then
ActiveSheet.UsedRange.Cells(2, Operation_Columns(j)).PasteSpecial Operation:=xlAdd
ElseIf Operation = "Subtraction" Then
ActiveSheet.UsedRange.Cells(2, Operation_Columns(j)).PasteSpecial Operation:=xlSubtract
ElseIf Operation = "Multiplication" Then
ActiveSheet.UsedRange.Cells(2, Operation_Columns(j)).PasteSpecial Operation:=xlMultiply
ElseIf Operation = "Division" Then
ActiveSheet.UsedRange.Cells(2, Operation_Columns(j)).PasteSpecial Operation:=xlDivide
Else
MsgBox "Enter either Addition, Subtraction, Multiplication, or Division as Operation."
End If
Next j
Next i
Application.CutCopyMode = False
End Sub
⧭ Output:
Run the code. It’ll pull data from the input worksheets by adding data from columns 2, and 3, into the worksheet called Combined Sheet (with Operation).
Read More: How to Extract Data from Excel Based on Criteria (5 Ways)
Things to Remember
Here, we’ve used the xlPasteAll property of the PasteSpecial method of VBA to copy everything from the source sheets to the destination sheets. Other than this, there are 11 more properties of the PasteSpecial method of VBA.
Click here to know them in detail.
While pulling large data sets, the codes may take a bit of time to run. So have patience and wait until they run successfully.
Conclusion
So these are all possible ways to pull data from multiple Excel worksheets into one worksheet using a Macro with VBA. Do you have any questions? Feel free to ask us. And don’t forget to visit our site ExcelDemy for more pasts and updates.
Related Articles
- Transfer Data from One Excel Worksheet to Another Automatically
- Excel Formula to Get First 3 Characters from a Cell(6 Ways)
- Extract Text After a Character in Excel (6 Ways)
- How to Extract Month from Date in Excel (5 Quick Ways)
- Extract Year from Date in Excel (3 Ways)
- How to Extract Specific Data from a Cell in Excel (3 Examples)
