How to Pull Data from Multiple Worksheets in Excel VBA

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

How to Pull Data from Multiple Worksheets in Excel VBA: 3 Easy Methods 

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 = False

Therefore, 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: Extract Data from One Sheet to Another Using VBA in Excel

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

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

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.

While pulling large data sets, the codes may take a bit of time to run. So have patience and wait until they run successfully.

Download Practice Workbook

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

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.

Related Articles

• Extract Filtered Data in Excel to Another Sheet
• How to Get Data from Another Sheet Based on Cell Value in Excel
• Pull Same Cell from Multiple Sheets into Master Column in Excel
• Excel Macro: Extract Data from Multiple Excel Files
• How to Pull Data from Multiple Worksheets in Excel
• How to Pull Values from Another Worksheet in Excel

<< Go Back To Extract Data Excel | Learn Excel