Pull lines from multiple tables based on matching criteria

[mharper90]

I have a workbook with several tables. Each table is for a specific equipment model. Then within the table are the inventoried serial numbers, the location of that specific item, and its calibration expiration date. These are the tables used for tracking overall inventory of each piece of equipment. This is an example of a portion of one of the tables. There are 37 tables like this within the workbook for many different models.

Model: EQ-265
Serial Number	Location	Cal Due Date	Initial	Notes
E00444	VAN	24-Apr-21
E00445	CAL	25-Feb-21
E00446	564	26-Jun-21
E00463	17	27-Oct-21
E00469	564	26-Jun-21
E00473	CAL	24-Aug-21
E00485	564	19-Nov-21
E00494	564	3-Apr-21
E00495	17	22-Jul-21
E00498	564	24-Apr-21

I would like to create a set of tables on another sheet in the workbook that reorganizes the information by location so that it's easier to see which items in a specific location are due for calibration. In the above example set, there are only 4 locations, but overall there are actually 11 possible locations. My ideal location summary table would look something like this. (Note: This table below has 2 lines extracted from the above table and the remaining lines would be extracted from other tables for different model numbers.)

Location: CAL
Model	Serial Number	Cal Due Date	Initial	Notes
EQ-265	E00445	25-Feb-21
EQ-265	E00473	24-Aug-21
EQ-549	E01332	19-Nov-21
EQ-549	B00064	3-Apr-21
EQ-549	E01386	22-Jul-21
LP-36	J00471	24-Apr-21

I'd prefer to do this without VBA, but am open to VBA alternatives if needed to accomplish the task. I've tried a few different varieties of Index and Match, but I just can't quite get anything to work.

 

[Takae]

If you accept VBA, please try this.
Paste on sheet module and type like just "CAL" on B2.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim w, buf, LR As Long, NLR As Long

    If Intersect(Target, Range("B1")) Is Nothing Then
        Exit Sub
    Else
        Application.EnableEvents = False
        Range(Range("A3"), Range("A3").SpecialCells(xlLastCell).Offset(1, 0)).ClearContents
        For Each w In Worksheets
            If w.Name <> ActiveSheet.Name Then
                LR = Cells(Rows.Count, 1).End(xlUp).Row + 1
                w.Range("A2").AutoFilter field:=2, Criteria1:=Target
                If WorksheetFunction.Subtotal(3, w.Range("B:B")) > 1 Then
                    buf = Replace(w.Range("A1").Value, "Model:", "")
                    w.Range(w.Range("A3"), w.Cells(Rows.Count, 1).End(xlUp)).Copy Cells(LR, 2)
                    w.Range(w.Range("C3"), w.Cells(Rows.Count, 3).End(xlUp)).Copy Cells(LR, 3)
                    NLR = Cells(Rows.Count, 2).End(xlUp).Row
                    Range(Cells(LR, 1), Cells(NLR, 1)).Value = buf
                    w.Range("A2").AutoFilter
                End If
            End If
        Next
    End If
    Application.EnableEvents = True
End Sub