Double Click to Filter Multiple Columns

[alexdowd2]

I have 2 sheets, one is a library of sorts (Sheet1) that has an Id column and then corresponding filter values for columns on Sheet2. That Id column from Sheet1 is NOT on Sheet2. Conceptually, what I want to be able to do is click on the Id column on Sheet1, then filter Sheet2 based on the values in that Id's row, which correspond to values in columns on Sheet2 (the column header names are the same). For example, sample data may look like this:

Sheet1

Id	Color	Make	Model
1​	Blue	Honda	Civic
2​	Gray	Ford	Taurus
3​	Green	Honda	Insight
4​	Green	Tesla	Model S

Sheet2

Color	Make	Model
Blue	Honda	Civic
Gray	Ford	Taurus
Green	Honda	Insight
Green	Tesla	Model S

Use Case: I double click 1 on Sheet1 and that filters Sheet2 on all records where Color = Blue, Make = Honda, Model = Civic.

 

[Fluff]

Hi & welcome to MrExcel.
How about

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
   If Target.Column = 1 Then
      Cancel = True
      If Target.Value = "" Then Exit Sub
      With Sheets("Sheet2").Range("A1")
         .AutoFilter 8, Target.Offset(, 1)
         .AutoFilter 9, Target.Offset(, 2)
         .AutoFilter 10, Target.Offset(, 3)
      End With
   End If
End Sub

 

[alexdowd2]

I'm getting a AutoFitler method of Range class failed error on .AutoFilter 8, Target.Offset(, 1)

 

[Fluff]

Oops forgot to change the fields after testing.
Change the 8,9 & 10 to 1,2 & 3 respectively.

 

[alexdowd2]

Perfect thank you!!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[alexdowd2]

You're welcome & thanks for the feedback.

Hi again, so I applied this to my real life scenario (same idea just more columns) and the filter is selecting the correct columns but instead of selecting the criteria it appears to be just clearing the filter (so it's removing all records), any ideas about that?

 

[Fluff]

Are you sure that there are rows which match all criteria?

 

[alexdowd2]

Are you sure that there are rows which match all criteria?

Yes, I isolated it to only affect 1 column to test that and I have the possible combinations based on a pivot table from the main data so I know all combos are there. When i manually select the filter no boxes are checked.

 

[Fluff]

Are you reading the values from the pivot?