Adjustment to existing Hyperlink Macro to Change address if a A Z sort filter is applied to a sheet.

[Elnicko]

This is the macro I'm using, and currently when I select an Item from a drop down list, it pushes adjecent cells to other worksheets, and places
a row reference in the master worksheet.

Is there a way to have that hyperlink update, if a sort filter is applied to the worksheet, where the data has been pushed to?

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim lNextRow As Long, ws As Worksheet, Found As Range
 
    If Target.Count = 1 And Target.Column = 9 Then
        If Target.Row >= 9 And Target.Row <= 1000 And Target.Row / 3 = Int(Target.Row / 3) Then
            If Target.Value <> "" Then
                'Test if Worksheet from drop down list exists
                On Error Resume Next
                    Set ws = ThisWorkbook.Worksheets(Target.Text)
                On Error GoTo 0
                If ws Is Nothing Then
                    MsgBox "Cannot locate a worksheet named " & Target.Text, vbExclamation, "Sheet Does Not Exist"
                Else
                    'Undo
                    For Each ws In Sheets(Array("Paul", "Rachel", "Julija", "Sue", "James"))
                        Set Found = ws.Columns("Z").Find(Target.Address(0, 0), , xlValues, xlWhole)
                        If Not Found Is Nothing Then
                            Found.EntireRow.Delete
                            Exit For
                        End If
                    Next ws
                    ' Log to selected sheet
                    With ThisWorkbook.Worksheets(Target.Text)
                        lNextRow = .Range("A" & .Rows.Count).End(xlUp).Row + 1
                        .Range("A" & lNextRow).Value = Target.Offset(, -1).Value
                        .Range("N" & lNextRow).Value = Target.Offset(, -3).Value & Target.Offset(, -2).Value
                        .Range("F" & lNextRow).Value = Target.Offset(, 1).Value
                        .Range("B" & lNextRow).Value = ActiveCell.Offset(, 2).Value
                        .Range("Z" & lNextRow).Value = Target.Address(0, 0)
                        'Hyperlink
                        ActiveSheet.Hyperlinks.Add Anchor:=Target.Offset(, 4), _
                                                   Address:="", _
                                                   SubAddress:="'" & Target.Text & "'!" & Rows(lNextRow).Address, _
                                                   TextToDisplay:="Row " & lNextRow
                    End With
                End If
            End If
        End If
    End If
End Sub

This is the code specific to the hyperlink.

                        'Hyperlink
                        ActiveSheet.Hyperlinks.Add Anchor:=Target.Offset(, 4), _
                                                   Address:="", _
                                                   SubAddress:="'" & Target.Text & "'!" & Rows(lNextRow).Address, _
                                                   TextToDisplay:="Row " & lNextRow

 

[Elnicko]

.

 

[Elnicko]

When the Macro is run it places a refference to the master sheet in Column z if that helps?