Macro to bring back rows from multiple sheets to one master sheet based on criteria

I think this does what you want.

Sub test_maras()
    Dim a(), shts
    Dim i As Integer
    Dim rng As Range
    Const wrd As String = "Open"
    
    shts = "Building Wide,B1 & B2,G Floor ,LG Floor ,1 Floor,2 Floor,3 Floor,4 Floor ,5 Floor ,6 Floor ,7 Floor ,8 Floor"
    shts = Split(shts, ",")
    Application.ScreenUpdating = False
    Sheets("Outstanding").[a4].CurrentRegion.Offset(1).ClearContents
    For i = 0 To UBound(shts) - 1
        With Sheets(shts(i))
            .[a9].CurrentRegion.AutoFilter 14, wrd
            If .[a9].CurrentRegion.Columns("n").SpecialCells(xlVisible).Rows.Count > 1 Then
                With .AutoFilter.Range
                    Set rng = .Offset(1, 0).Resize(.Rows.Count - 1).SpecialCells(xlVisible)
                    With Sheets("Outstanding")
                       rng.Copy .Range("a" & .Cells(Rows.Count, "a").End(3).Row)(2)
                    End With
                End With
            End If
            .AutoFilterMode = False
        End With
    Next
    Application.CutCopyMode = False
    Set rng = Nothing
End Sub

Glad to help. Can you please mark as solved.