Excel VBA - Double-clicking a cell in a sheet to generate filtered data on a nother sheet within the workbook

Question

I have a workbook that I'm creating that will serve a temporary CRM database for bank clients.

Sheet1 is named Company

Each row on Sheet1 has fields for details like phone, email, and address but also fields that require another sheet to include the necessary info such as Contacts, Accounts, and History (a log of phone and email contacts with the client).

What I am trying to do is create the VBA code on the Company sheet that will take me to the data on the respective specific sheets filtered by that company name.

For example; if I double click on a contact's name on the Company sheet, it would take me the Contacts sheet and filter that sheet for only companies that match the company of the contact I double clicked.

I'm new to VBA and got the code from this website Clicking a hyperlink in Excel to set autofilter on a different sheet The thread was pretty old so I thought it would be better to create a new one.

I keep getting "Run-time error '9': Subscript out of range' and the debugger highlights the row that starts with 'Sheet2.ListObjects(ContactTable)

Here is the code I have so far:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If ActiveCell.Column = 9 Then
    Sheet2.ListObjects(ContactTable).Range.AutoFilter Field:=1, Criteria1:=ActiveCell.Offset(0, -8).Value
    Sheet2.Activate
    End If
End Sub

Column1 on all pages is 'Company" header name

Sheet1 is named Company, Sheet2 is named Contacts

The table on Sheet1 is named CompanyTable, the table on Sheet2 is named ContactTable

I have autofilters on all sheets

Any help with my code would be much appreciated. Please let me know if you need any other data on my workbook.

Answer 1

Sheet2.ListObjects("ContactTable") - that should do the trick.