Double-click event macro auto filter multiple columns on different worksheet

[ac7]

Hello -

I am new to the forum and am hoping someone can help me with this question. I have a spreadsheet with 2 worksheets: Orders, and Filtered.

Orders:

Filtered:

This is really oversimplified, but I am unable to upload my full spreadsheet. I am looking to have a double-click event macro (or whatever else would be appropriate) trigger on the Orders worksheet when an order number is double-clicked. I would then like to have the Filtered worksheet automatically filter to the order number and also filter the Short? column to only "Y". So if order 164893 on the Orders worksheet was double-clicked, I'd like to only see "Y" shortages for that specific order on the Filtered worksheet.

Thank you in advance for the help!

 

[kevin9999]

Please try the following on a copy of your workbook. Right-click on the Orders tab name, select View Code, and paste the code below into the window that appears on the right of the screen. Save the file as a macro-enabled workbook.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Target.Column <> 1 Then Exit Sub
    Application.EnableEvents = False
    Cancel = True
    Dim s As String: s = Target.Value2
    If s <> "" Then
        With Worksheets("Filtered").Range("A1").CurrentRegion
            .AutoFilter 1, s
            .AutoFilter 2, "Y"
        End With
        Application.Goto Worksheets("Filtered").Range("A1")
    End If
    Application.EnableEvents = True
End Sub

The Filtered sheet before double clicking on the 123456 order on the Order sheet:

Book1
	A	B
1	Orders	Short?
2	123456	Y
3	123456	Y
4	158285	N
5	164893	Y
6	164893	N
7	164893	Y
Filtered

And after double clicking on the 123456 order on the Order sheet:

Book1
	A	B
1	Orders	Short?
2	123456	Y
3	123456	Y
8
Filtered

 

[ac7]

This worked wonderfully, thank you very much!

 

[kevin9999]

This worked wonderfully, thank you very much!

Happy to help and thanks for the feedback

 

[ac7]

Actually, one other question. I would like to add this auto filter to another worksheet called "Purchases". It will also filter the Filtered worksheet, but different columns. I was able to modify the code just fine, however, if I ran the macro from the Orders worksheet first, it's not filtering correctly because it's not removing the existing filter on column A. Is there a line of code I can enter that will clear the existing filters on column A or column S on the Filtered worksheet before running the rest of the macro?

 

[kevin9999]

Actually, one other question. I would like to add this auto filter to another worksheet called "Purchases". It will also filter the Filtered worksheet, but different columns. I was able to modify the code just fine, however, if I ran the macro from the Orders worksheet first, it's not filtering correctly because it's not removing the existing filter on column A. Is there a line of code I can enter that will clear the existing filters on column A or column S on the Filtered worksheet before running the rest of the macro?

Immediately after If s <> "" Then put this:
Worksheets("Filtered").AutoFilter.ShowAllData

 

[ac7]

Works perfectly, thanks again for the help.