Filter by value in cell to the left.

[JoanneA]

Hi,

I am very new to VBA(Day 4 of using it).
Here is what I am trying to do...

Sheet 1 is a table with the first column and first row as text (table headings). The first column is names and first row is from report names. Namereport 1report 2Jane Doe114John Doe52


Sheet 2 is the report1, for example purposes
NameJane DoeJane DoeJane DoeJane DoeJane DoeJane DoeJane DoeJane DoeJane DoeJane DoeJane DoeJohn DoeJohn DoeJohn DoeJohn DoeJohn Doe

I am trying to have it to so that when you double click on cell B2 of sheet1, it will go to sheet2 and autofilter on the value to the left of B2 on sheet1 "Jane Doe"

This is what I have so far but when I double click on B2 it just moves the cursor to the cell to the left instead of filtering on the value of the cell to the left on sheet2.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) 
    Dim id As String 
    If IsEmpty(ActiveCell) = False Then 
        ActiveCell.Offset(0, -1).Select 
    Else 
        id = CStr(Selection) 
        Sheet2.Activate 
        Sheet2.Range("A1:A500").AutoFilter Field:=1, Criteria1:=IsEmpty(ActiveCell) = False And ActiveCell.Offset(0, -1).Value.id 
    End If 
End Sub 
 

VBA tags courtesy of www.thecodenet.com

Any help is very appreciated!!
Thank you!!