Auto Filter VBA

[kac1125]

Hello, I am trying to build a VBA that will filter sheet 5 Column U if I double click the value in sheet 2 Column A. Any help would be greatly appreciated!!
Thanks in advance!!

 

[Joe4]

Does your data on Sheet 5 begin with a header in row 1 and data starting on row 2?
Is column U populated for every row on Sheet 5 with data?
What columns are to be included in your filter?

 

[kac1125]

Does your data on Sheet 5 begin with a header in row 1 and data starting on row 2?
Is column U populated for every row on Sheet 5 with data?
What columns are to be included in your filter?

Hello, Yes there is a header in sheet 5 data starting in row 2. Column U is populated with Data for all rows. All columns are included in the filter but want filter to be based on value in Column U as this matches Column A on sheet 2. If that makes sense.

Thanks!

 

[Joe4]

OK, the code may require some tweaks because you did not answer all m y questions (which columns are involved correctly). If U is not your last column in your Filter range, you will need to change that value in the Filter line of code.

You want to go to Sheet2, right-click on the sheet tab name at the bottom of the screen, select "View Code", and paste this VBA code in the VB Editor that pops-up.
It should then do what you want whenever you double-click on a value in column A below row 1 on Sheet 2.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

    Dim f As Variant
    Dim ws5 As Worksheet
    Dim lr As Long
    
'   Ignore row 1
    If Target.Row = 1 Then Exit Sub
    
'   Ignore anything outside of column A
    If Target.Column > 1 Then Exit Sub
    
'   Set worksheet variable
    Set ws5 = Sheets("Sheet5")
    
'   Grab value to use in filter
    f = Target.Value
    
'   Find last row with data on Sheet5
    lr = ws5.Cells(ws5.Rows.Count, "U").End(xlUp).Row
    
'   Apply to filter on Sheet 5
    ws5.Activate
    ws5.Range("A1:U" & lr).AutoFilter Field:=21, Criteria1:=f

End Sub

 

[kac1125]

Worked perfectly!! Thank you!

 

[Joe4]

You are welcome.
Glad I was able to help!