multiple sheets in macro

[member23]

Hello to you all

My question is simple but i just can't find something that works. This code is to get data from one sheet to another upon opening the workbook. The thing is that i want it to get data from multiple sheets not just one, so how could i get that number of sheets inside the code? The sheet with data is Sheet 3 (in which the rest have to be added) and Sheet 2 is where data should go. Thanks a lot for your time guys..






Private Sub Workbook_Open()
Dim i, LastRow

LastRow = Sheets("Sheet3").Range("A" & Rows.Count).End(xlUp).Row
Sheets("Sheet2").Range("A2:I500").ClearContents
For i = 2 To LastRow
If Sheets("Sheet3").Cells(i, "E").Value = "" Then
Sheets("Sheet3").Cells(i, "E").EntireRow.Copy Destination:=Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
End If
Next i
End Sub

 

[Michael M]

UNTESTED

Private Sub Workbook_Open()
Dim i As Long, LastRow As Long, ws As Worksheet
Sheets("Sheet2").Range("A2:I500").ClearContents
For Each ws In Worksheets
    If ws.Name <> ("Sheet2") Then
        ws.Activate
        LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row
            For i = 2 To LastRow
                If ws.Cells(i, "E").Value = "" Then
                    Rows(i).Copy Destination:=Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
                End If
            Next i
    End If
Next ws
End Sub

 

[member23]

Thank you so much Michael M! You made my day!!Take care and be safe

 

[Michael M]

Thanks, glad to help...