Let´s say you do a lot of searches in two tables. The tables are related so it would be great if the second table is simultaneously filtered depending on the filtered values from the first table.


You want to know the contact information to all vendors in product module 3. You select Module 3 in column "Product module" and vendor names appear in column "Vendor".

But contact information to each vendor is in table 2, sheet "Vendors". The macro demonstrated below filters table2 automatically.

The following  vba code is executed when sheet "Vendors" is activated.

Private Sub Worksheet_Activate()
Dim temp() As Variant
Dim rng As Range
Dim b As Boolean
Dim i As Single
ReDim temp(0)
Application.ScreenUpdating = False
Set rng = Worksheets("Modules").ListObjects("Table1").ListColumns(4).Range
'Copy filtered values from Table1 to dynamic array
For i = 2 To rng.Cells.Count
    If rng(i).Value <> "" Then
        If rng(i).EntireRow.Hidden = False Then
            temp(UBound(temp)) = rng(i).Value
            ReDim Preserve temp(UBound(temp) + 1)
            b = True
        End If
    End If
Next i
ReDim Preserve temp(UBound(temp) - 1)
'Remove previously selected filters in table2
Worksheets("Vendors").ListObjects("Table2").Range.AutoFilter Field:=1
If b <> True Then Exit Sub
'Apply filtered values to table 2
Worksheets("Vendors").ListObjects("Table2").Range.AutoFilter _
    Field:=1, Criteria1:=temp, Operator:=xlFilterValues
Application.ScreenUpdating = True
End Sub

Download excel *.xlsm file
Search two tables simultaneously.xlsm