none
Filter by order number on multiple sheets by doubleclicking ordern umber on first sheet RRS feed

  • Question



  • I've got 3 worksheets with tables. All 3 tables contains column named OrderNumber.
    I'm trying to write a code that would filter tables in 2nd and 3rd sheet by clicking
    or double clicking on OrderNumber in 1st table.

    I have code that is working but filters me only 2nd sheet.
    So if I double click ordrenumber M024 for example, it filers me all rows with M024 in 2nd sheet.
    I would like the code to filter ordernumber also in 3rd sheet.

    What do i have to add, that this would work in both (2nd and 3rd) sheets.


    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim strCellVal As String
    Dim i As Long

        If Target.Cells.Count <> 1 Then Exit Sub

        If Not Intersect(Target, [Tabele1[@OrderNumber]]) Is Nothing Then
        strCellVal = Target.Value

        With Sheet2
            .ListObjects(1).ShowAutoFilter = False
            .ListObjects(1).Range.AutoFilter 1, Criteria1:=Target.Value
            .Activate
        End With

        Cancel = True
        End If
        
    End Sub


    Thanks, Al
    Friday, August 22, 2014 7:59 AM

Answers

  • Hello,

    I added some lines to your code:

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
     Dim strCellVal As String
     Dim i As Long
     
     Dim Sheet2 As Worksheet, Sheet3 As Worksheet
     
     Set Sheet2 = ThisWorkbook.Worksheets("Blad1")
     Set Sheet3 = ThisWorkbook.Worksheets("Blad2")
     
        If Target.Cells.Count <> 1 Then Exit Sub
     
        If Not Intersect(Target, [Tabele1[@OrderNumber]]) Is Nothing Then
         strCellVal = Target.Value
     
        With Sheet2
             .ListObjects(1).ShowAutoFilter = False
             .ListObjects(1).Range.AutoFilter 1, Criteria1:=Target.Value
             .Activate
         End With
        
         With Sheet3
             .ListObjects(1).ShowAutoFilter = False
             .ListObjects(1).Range.AutoFilter 1, Criteria1:=Target.Value
             .Activate
         End With
     
        Cancel = True
         End If
        
     End Sub

    I needed a reference to your sheets:

     Dim Sheet2 As Worksheet, Sheet3 As Worksheet

    Set Sheet2 = ThisWorkbook.Worksheets("Blad1")
    Set Sheet3 = ThisWorkbook.Worksheets("Blad2")

    replace  Blad1 and Blad2 with the name of your sheets.

    Hope this works for you

    • Marked as answer by allsux Friday, August 22, 2014 9:39 AM
    Friday, August 22, 2014 8:33 AM

All replies

  • Hello,

    I added some lines to your code:

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
     Dim strCellVal As String
     Dim i As Long
     
     Dim Sheet2 As Worksheet, Sheet3 As Worksheet
     
     Set Sheet2 = ThisWorkbook.Worksheets("Blad1")
     Set Sheet3 = ThisWorkbook.Worksheets("Blad2")
     
        If Target.Cells.Count <> 1 Then Exit Sub
     
        If Not Intersect(Target, [Tabele1[@OrderNumber]]) Is Nothing Then
         strCellVal = Target.Value
     
        With Sheet2
             .ListObjects(1).ShowAutoFilter = False
             .ListObjects(1).Range.AutoFilter 1, Criteria1:=Target.Value
             .Activate
         End With
        
         With Sheet3
             .ListObjects(1).ShowAutoFilter = False
             .ListObjects(1).Range.AutoFilter 1, Criteria1:=Target.Value
             .Activate
         End With
     
        Cancel = True
         End If
        
     End Sub

    I needed a reference to your sheets:

     Dim Sheet2 As Worksheet, Sheet3 As Worksheet

    Set Sheet2 = ThisWorkbook.Worksheets("Blad1")
    Set Sheet3 = ThisWorkbook.Worksheets("Blad2")

    replace  Blad1 and Blad2 with the name of your sheets.

    Hope this works for you

    • Marked as answer by allsux Friday, August 22, 2014 9:39 AM
    Friday, August 22, 2014 8:33 AM
  • Works perfect

    Thank you

    Friday, August 22, 2014 9:39 AM