Double-click on one Excel sheet filters another sheet to that value

Question

I've tried to follow this link: Clicking a hyperlink in Excel to set autofilter on a different sheet And this other follow up to it: Autofilter a table in another sheet based on selected cell value where autofilter needs to be 'contains'

But I'm newer and more lost than these others. I have 2 sheets. Accounts (Sheet1). One row per account (no duplicates). CustomerNames (Sheet2) may have multiple rows/names per account.

What I want to do is click on the Account Name (F2 = column header) in Sheet1, have it read the cell before it (column E) which has a number (since names may not be identical on my sheet2)

Then sheet 2 has a bunch of customer names at the account. I want this table to filter to the account number that corresponds to what was double clicked on sheet1 column E. On sheet 2 the matching data to sheet1 column E is in column D.

My headers are in row 2 and my tables are set to start with row 2 on both sheets.

I can't even get the double clicking to do anything. It doesn't even seem like my macro tries to run. I don't know that it would help to paste what I've tried. I just tried editing the other VBA I found on the links I mentioned.

Do I need to do something to set up the double click part?

My code:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If ActiveCell.Offset(0, -1) = 6 Then
        Sheet2.ListObjects("CustomerNames").Range.AutoFilter Field:=4, Criteria1:=ActiveCell.Value
        Sheet2.Activate
    End If
End Sub

Can you explain why the If ActiveCell.Offset(0, -1) = 6 Then? That doesn't seem to match your question description. — BigBen
I really have no clue what I'm doing and reading something else it seemed like I needed to offset the data to look at....basically, the people using this will click on the account name b/c the # means nothing to them and will be hidden but the numbers on each sheet are what match and if I was doing a vlookup these numbers would be the connection... — AVK
So, for clarification, the =6 doesn't have any significance? — BigBen
Well I thought it was the reference to the column that the account name in sheet 1 is in (column F) where people will click. — AVK
Setting up the double-click part, which is your actual question. In which code module is the code in the question? Have you set up a procedure to activate events? You need to research in the VBA Help how to work with events. — Cindy Meister

BigBen BigBen · Accepted Answer · 2019-11-18T21:20:50

If I understand your question, try something like this:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Target.Column = 6 Then
        Sheet2.ListObjects("CustomerNames").Range.AutoFilter Field:=4, Criteria1:=Target.Offset(,-1).Value
        Sheet2.Activate
    End If
End Sub

Double-click on one Excel sheet filters another sheet to that value

1 Answers