How To Pull Data From Multiple Worksheets In Excel Vba

How to pull data from multiple worksheets in Excel VBA

In Excel VBA, you can easily pull data from multiple worksheets by using the Worksheets object and referencing the desired worksheets. Here’s an example:

    
Sub PullDataFromWorksheets()
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim ws3 As Worksheet
    Dim ws4 As Worksheet
    Dim rng1 As Range
    Dim rng2 As Range
    Dim rng3 As Range
    Dim rng4 As Range
    Dim outputSheet As Worksheet
    Dim lastRow As Long

    ' Set references to the worksheets you want to pull data from
    Set ws1 = ThisWorkbook.Worksheets("Sheet1")
    Set ws2 = ThisWorkbook.Worksheets("Sheet2")
    Set ws3 = ThisWorkbook.Worksheets("Sheet3")
    Set ws4 = ThisWorkbook.Worksheets("Sheet4")

    ' Set references to the ranges you want to pull data from
    Set rng1 = ws1.Range("A1:C10")
    Set rng2 = ws2.Range("A1:D5")
    Set rng3 = ws3.Range("B2:G8")
    Set rng4 = ws4.Range("C3:F12")

    ' Set reference to the output sheet where you want to paste the data
    Set outputSheet = ThisWorkbook.Worksheets("OutputSheet")

    ' Find the last row in the output sheet
    lastRow = outputSheet.Cells(outputSheet.Rows.Count, 1).End(xlUp).Row

    ' Copy data from the ranges and paste it into the output sheet
    rng1.Copy outputSheet.Cells(lastRow + 1, 1)
    rng2.Copy outputSheet.Cells(lastRow + 1, 1)
    rng3.Copy outputSheet.Cells(lastRow + 1, 1)
    rng4.Copy outputSheet.Cells(lastRow + 1, 1)
    
    MsgBox "Data copied successfully!"
End Sub
    
  

In the above example, we have four worksheets (Sheet1, Sheet2, Sheet3, and Sheet4) from which we want to pull data. We specify the ranges we want to pull data from for each worksheet and set references to the output sheet where we want to paste the data.

The “lastRow” variable is used to find the last row in the output sheet, which allows us to append the data from each range below the existing data.

Finally, we use the Copy method to copy the data from each range and paste it into the output sheet using the Paste method. The data is pasted starting from the first empty row in the output sheet.

After running the macro, a message box will appear confirming that the data has been copied successfully.