Sorting and Filter in Multiple Sheets using Hyperlinks

[Sheikh Saadi]

Hi,
I have an Excel Workbook containing multiple sheets.
Sheet1 contains my Customers data consisting CustID, CustName, CustAdd,
CustPhone etc.
Sheet2 contains Orders placed by customers consisting CustID, OrderID,
OrderDate etc.
I have CustID field common in both the sheets. Now I want that CustID in
Sheet1 gets hyperlinked with CustID field in Sheet2. Meaning, when I click on
CUS0005 in Sheet1, it will jumps to Sheet2 and filter out the data only for
CUS0005 in Sheet2.
I think this could be done. Plz tell me if I am making any sense.

Thanks,

 

[Nigel]

You need to apply an autofilter in sheet2, the filter would be set by the
selected CustID chosen on sheet1.

To make this happen use the before-double-click event on sheet1, see example
code below
..
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
' column of the CustID assumed to be 1 (col A)
Dim sCID As String
If Target.Column = 1 Then
sCID = Trim(Target.Value)

' set filter range for sheet 2 all orders data
With Sheets("Sheet2")
.Range("A1:F500").AutoFilter Field:=1, Criteria1:=sCID
.Activate
.Range("A1").Select
End With

End If
End Sub

 

[Sheikh Saadi]

Thanks Nigel,
It worked... and completely fulfilled my needs. I was wrting a code which
was working (through hyperlinks) though making my worksheet mad... Your
solution is simple and without any problems... thanks a lot...