VBA AutoFilter with Hyperlink

[ADSkinner]

Hello all,

So I have run into a snag on a file I have put together.

I have added the following code into a module:

Sub Filter(lField As Long, sCriteria As String)   Worksheets("Shortage_Report").Range("C1").AutoFilter Field:=lField, _
      Criteria1:=sCriteria
End Sub

And then on the worksheet that pulls the data for the filter:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Call Filter(lField:=3, sCriteria:=Target.Parent.Value)
End Sub

On the worksheet that pulls the data for the filter, I have created a hyperlink so that upon clicking that value, it takes the user to the link, and filters on that target value. All works wonderfully, however (hence the reason I am posting), when I copy the hyperlink down my column and attempt to click on one of those, I get an error on this line of code

Call Filter(lField:=3, sCriteria:=Target.Parent.Value)

But....if I were to go back and then set up the hyperlink like I did the first time, it works. This would be a fine resolution, if I didn't need have numerous cells that would be set up with the hyperlink.

I am looking for a way, if possible, to where I can copy the hyperlink down the column.
With the error resonating in my code for the auto filter, is there something missing there?

Any thoughts and/or help would be greatly appreciated.

 

[ADSkinner]

Does anyone have any thoughts on this?

 

[ADSkinner]

Okay, so solved my issue. Instead of creating hyperlinks, I used code to autofilter based on double click

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)    Cancel = True
    With Worksheets("Shortage_Report")
        .Select
        .Range("C1").AutoFilter Field:=3, Criteria1:=Target.Value
        End With
End Sub

However now, I have run into a new issue. Once the auto-filter has been applied to the other worksheet, and I return to the first sheet that had the value to be filtered, that column is not updating. I can use F9 to force it, but I would rather have it update on it's own.

I have also tried

Worksheets(1).Calculate

but that is also not working. Anyone have any thoughts?