PDA

View Full Version : Filter by value in cell to the left.



JoanneA
04-30-2012, 07:49 AM
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



Any help is very appreciated!!
Thank you!!