Hyperlink click "cell text" to next sheet and auto filter by the "same clicked text" VBA

Good evening,

is it possible to have a clicked text hyperlinked to next sheet and auto filter by the same text simultaneously using VBA?

example, "sheet1"click column B4 brings me to "sheet2 C:C C2 auto filter ", then appear "same result as sheet 1 B4"



thank you for the help~

Try this code in module1

Sub filter(Item As String)
'
'
Worksheets("2").Activate
ActiveSheet.AutoFilterMode = False
ActiveSheet.Range("$A$2:$R$5171").AutoFilter Field:=3, Criteria1:=Item
End Sub

And this code in sheet "1"

Private Sub Worksheet_Change(ByVal Target As Range)

Dim CellAdd As String

If Target.Count > 1 Then Exit Sub

If Not Intersect(Target, Range("B:B")) Is Nothing Then
Worksheets("2").AutoFilterMode = False
CellAdd = "$C$" & Application.Match(Target.Value, Worksheets("2").Range("C1:C999"), 0)
ActiveCell.Offset(-1, 0).Select
ActiveCell.Hyperlinks.Add Anchor:=ActiveCell, Address:="", SubAddress:="'2'!" & CellAdd, ScreenTip:="", TextToDisplay:=Target.Value
Call filter(ActiveCell.Value)
End If
End Sub

When you enter an Item Code in column B in sheet "1", it will create a hyperlink to Sheet "2" and filter on the entered item number.

Is that what you wanted?

Originally Posted by Croweater

Try this code in module1

Sub filter(Item As String)
'
'
Worksheets("2").Activate
ActiveSheet.AutoFilterMode = False
ActiveSheet.Range("$A$2:$R$5171").AutoFilter Field:=3, Criteria1:=Item
End Sub

And this code in sheet "1"

Private Sub Worksheet_Change(ByVal Target As Range)

Dim CellAdd As String

If Target.Count > 1 Then Exit Sub

If Not Intersect(Target, Range("B:B")) Is Nothing Then
Worksheets("2").AutoFilterMode = False
CellAdd = "$C$" & Application.Match(Target.Value, Worksheets("2").Range("C1:C999"), 0)
ActiveCell.Offset(-1, 0).Select
ActiveCell.Hyperlinks.Add Anchor:=ActiveCell, Address:="", SubAddress:="'2'!" & CellAdd, ScreenTip:="", TextToDisplay:=Target.Value
Call filter(ActiveCell.Value)
End If
End Sub

When you enter an Item Code in column B in sheet "1", it will create a hyperlink to Sheet "2" and filter on the entered item number.

Is that what you wanted?

Hi Croweater,

I will have multiple items in row B in "sheet 1",

i putted codes into designated place and come up with error , unkown name "filter"

I re-uploaded the xlsm file onto the first post, please assist,

thank you very much.

Look at the code in your sheet.

Sub filter()

Sub filter(Item As String)

Worksheets("2").Activate
ActiveSheet.AutoFilterMode = False
ActiveSheet.Range("$A$2:$R$5171").AutoFilter Field:=3, Criteria1:=Item
End Sub

Can you see how this is different to what I put up?

Hint: Remove the first line.



You may also want to try this modification in Sheet1. Not sure how you want it to work.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim CellAdd As String
On Error GoTo exitsub

    If Target.Count > 1 Then Exit Sub

    If Not Intersect(Target, Range("B:B")) Is Nothing Then
        Worksheets("2").AutoFilterMode = False
        CellAdd = "$C$" & Application.Match(Target.Value, Worksheets("2").Range("C1:C999"), 0)
        ActiveCell.Offset(-1, 0).Select
        ActiveCell.Hyperlinks.Add Anchor:=ActiveCell, Address:="", SubAddress:="'2'!" & CellAdd, ScreenTip:="", TextToDisplay:=Target.Value
        Call filter(ActiveCell.Value)
    End If

exitsub:
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Intersect(Target, Range("B:B")) Is Nothing Then
        If Target.Value = "" Then Exit Sub
        If Target.Hyperlinks.Count > 0 Then
            Call filter(ActiveCell.Value)
        End If
        
    End If
End Sub