Filter data by double clicking on any cell in range A3:A11 on any worksheet in workbook

**rgullett** · 05-22-2013, 12:25 PM

In the attached the code works for the item number located in Cell A1 on worksheet "801" when double clicked

I would like to filter the "Data" worksheet by double clicking on any item # in range "A3:A11" on any of the other worksheets "801,802,803". Original workbook that I will adapt code to has 45+ worksheets.
Item #s are unique.

Private Sub Workbook_sheetBeforeDoubleClick(ByVal sh As Object, ByVal Target As Range, Cancel As Boolean)
If Intersect(Target, Range("A1")) Is Nothing Then
Else
    Sheets("Data").Select
    Selection.AutoFilter Field:=4, Criteria1:=Worksheets("801").Range("A1").Value
End If
End Sub

Thank you for your assistance

**tehneXus** · 05-22-2013, 05:18 PM

try

Option Explicit

Private Sub Workbook_sheetBeforeDoubleClick(ByVal sh As Object, ByVal Target As Range, Cancel As Boolean)
    If Not Intersect(Target, sh.Range("A3:A11")) Is Nothing And sh.Name <> "Data" Then
        With Worksheets("Data")
            If Not .AutoFilterMode Then .AutoFilter
            .AutoFilter.ShowAllData
            .Cells(1, 1).CurrentRegion.AutoFilter Field:=4, Criteria1:=Target.Value
            .Activate
        End With
        Cancel = True
    End If
End Sub

**rgullett** · 05-22-2013, 06:53 PM

tehneXus,
Many thanks,
the code works beautifully.

RG

Filter data by double clicking on any cell in range A3:A11 on any worksheet in workbook

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Filter data by double clicking on any cell in range A3:A11 on any worksheet in workbook

Re: Filter data by double clicking on any cell in range A3:A11 on any worksheet in workboo

Re: Filter data by double clicking on any cell in range A3:A11 on any worksheet in workboo

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions