Follow Hyperlink to Separate Workbook and AutoFilter by Value in Source Workbook

Question

I have a code that allows me to follow a hyperlink to a separate sheet within the same workbook and filter such sheet by a value associated with my hyperlink. Note that I create the hyperlink first and outside of the VBA process. This is how I did it:

a) Open Visual Basic under Developer tab

b) Right-Click the workbook you want to add the macro to and click Insert >Module

c) Copy and paste the following code into the module:

 Sub Filter(sCriteria As String)

lField = Cells(1, 1).EntireRow.Find("Isometric Number", LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False).Column
Range("a1").AutoFilter Field:=lField, _
Criteria1:=sCriteria

End Sub

My understanding is that this module tells the workbook to autofilter the column named Isometric Number on the hyperlinked tab by a particular value that is called out below. You can change the column name to whatever you want, but it needs to be changed in the Module and on the tab where you want the autofilter to occur.

D) Copy and paste the following code to your main tab (expand Microsoft Excel Objects and double click your main tab - where the hyperlink and what I'm filtering by lives).

Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
   Call Filter(sCriteria:=Cells(Target.Parent.Row, 2).Value)
End Sub

This code determines the filter criteria for the module above to auto filter by. Right now it is set up to filter by the value in the cell in the same row as the hyperlink and in the 2nd column.

Now finally for the question: When my hyperlink refers to a separate workbook (instead of a sheet within the same workbook) I get the following error : Object variable or With block variable not set (Error 91). When I run the debug tool this is the line that gets highlighted:

lField = Cells(1, 1).EntireRow.Find("Isometric Number", LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False).Column

I've done quite a bit of reading on this but I'm having difficulties figuring out how to read and understand the code. The closest thing to an answer has been this post: Can Excel vba invoke an autofilter when a hyperlink is clicked to open new workbook?

SJR SJR · Accepted Answer · 2016-11-17T20:59:07

Try changing this line, as it will currently be referencing the the newly-opened workbook. You may need to change the sheet index (or name)

lField = ThisWorkbook.Sheets(1).Cells(1, 1).EntireRow.Find("Isometric Number", LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False).Column

Follow Hyperlink to Separate Workbook and AutoFilter by Value in Source Workbook

1 Answers