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

VBA Code to Pull Data from Multiple Worksheets to One Worksheet Horizontally


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.

Worksheets to Pull Data from Multiple Worksheets in Excel VBA

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

VBA Code to Pull Data from Multiple Worksheets to One Worksheet Horizontally

⧭ 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

VBA Code to Pull Data from Multiple Worksheets in Excel VBA

⧭ Output:

Run the code. It’ll pull data from all the worksheets horizontally in the destination worksheet (Combined Sheet (Horizontally) here).

to Pull Data from Multiple Worksheets in Excel VBA Output

Read More: How to Pull Data From Another Sheet Based on Criteria in Excel


Similar Readings


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

Rifat Hassan

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo