VBA to filter based on a cell value

[cee_real]

Hello,

I'm trying to filter two tables on a separate worksheet based on a value chosen from a drop down on my first worksheet.

Worksheet 1
-Drop Down Reference Cell G2

Worksheet 2
-Table 1 Range B21:Q116.
-Filter for column C21

Table 2 Range B122:Q216
-Filter for column C122

Any help is appreciated.

 

[Peter_SSs]

See if this does what you want. Test in a copy of your workbook.

To implement ..
1. Right click name tab of Sheet1 and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1. (Check the sheet name in the code is the same as your sheet with the tables. The table names should also be checked.)
3. Close the Visual Basic window & test.
4. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm).

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim sCrit As String
  
  If Not Intersect(Target, Range("G2")) Is Nothing Then
    With Sheets("Sheet2")
      .ListObjects("Table1").Range.AutoFilter Field:=2
      .ListObjects("Table2").Range.AutoFilter Field:=2
      sCrit = Range("G2").Value
      If Len(sCrit) > 0 Then
        .ListObjects("Table1").Range.AutoFilter Field:=2, Criteria1:=sCrit
        .ListObjects("Table2").Range.AutoFilter Field:=2, Criteria1:=sCrit
      End If
    End With
  End If
End Sub

 

[cee_real]

This worked. Thank YOU!

 

[Peter_SSs]

This worked. Thank YOU!

You are welcome. Thanks for letting us know.