How to double click on a cell and go to another sheet filtering the data

[gigix89]

Hi Guys

I would need your help. I have one file excel with two sheets. One are the orders to be shipped and the other one is the inventory. Both sheets are extraction from an ERP system

I would like to write a macro that with a double click on an ID product in the sheet "orders to be shipped" goes in the other sheet "inventory" filtering the data.

Can you help me?

thanks
Luigi

 

[offthelip]

You haven't given any details as to what columns you need. So I assumed a list on sheet1 for the list to click and I assumed the data was in sheet2 colums C and D to filter.
put this code in the sheet1 code :

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
filv = Target.Value
Call filt(filv)
End Sub

and put this code in a standard module:

Sub filt(fval As Variant)
    Worksheets("Sheet2").Select
    Columns("C:C").Select
    Selection.AutoFilter
    ActiveSheet.Range("$C$2:$D$34").AutoFilter Field:=1, Criteria1:=fval

End Sub

Adjust fitler etc to your requirements

 

[gigix89]

Hi @offthelip thanks for your reply.

here the sheet one, where you see the Cd_AR, which is the product that I am going to double click.

while here the sheet to that I want to be filtered in automatically

 

[Fluff]

How about

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
   If Target.Column = 8 Then
      Cancel = True
      With Sheets("OC_vs_Giancenza")
         .Range("A1").AutoFilter 1, Target.Value
         Application.Goto .Range("A1"), True
      End With
   End If
End Sub

This needs to go in the sheet module where you want to double-click

 

[gigix89]

Hi @Fluff super!it worked perfectly. Thanks a lot

 

[Fluff]

Glad we could help & thanks for the feedback.