Macro help to consolidate cells with data into Master

[Barbare11a]

Hi,

I've got a file with multiple tabs that I'd like to copy-paste values from into a summary tab. I found the thread VBA - Copy and Paste Range From Multiple Sheets into Summary from Oct 2021 and The response from My Answer is This is brilliant and has solved 95% of my query.

My data is a standard table from B:S that pulls from other locations. To ensure we have all the data coming across, there are lots more rows than required, and the rows just return 0s when the base data is finished.

The below code will copy a set range from each nominated sheet. What I would like to do is have it copy and paste-values for all cells where there is "active data", IE not a row of 0s.

I think there may be a few ways to do this, but am a bit out of my depth and the things I have tried have failed:

Option1 - search down column B until you find a 0, then copy everything from B3: downwards to the row before finding the 0
Option2 - Copy all active cells (anything in column B) and then another macro? to search the summarised data (Tab = "Summary") and delete all rows where b=0

Any help is greatly appreciated!

Current VBA Code:

Sub Consolidate_States()
On Error GoTo M
Application.ScreenUpdating = False
Dim i As Long
Dim ans As String
Dim lastRow As Long
Dim Lastrowa As Long
lastRow = Sheets("Master").Cells(Rows.Count, "A").End(xlUp).Row
Lastrowa = 8

For i = 2 To lastRow
    ans = Sheets("Master").Cells(i, 1).Value
    
    With Sheets(ans)
        .Range("A3:S238").Copy
        Sheets("Summary").Cells(Lastrowa, 1).PasteSpecial xlPasteValues
        Lastrowa = Sheets("Summary").Cells(Rows.Count, "b").End(xlUp).Row + 1
    End With
Next
Application.ScreenUpdating = True
Exit Sub
M:
MsgBox "You tried to use a sheet name that does not exist" & vbNewLine & "Or we had another problem"
Application.ScreenUpdating = True
End Sub