Excel VBA Follow Hyperlink to Separate Workbook and AutoFilter by Value in Source Workbook -


i'm totally new vba please bare me. have code allows me follow hyperlink separate sheet within same workbook , filter such sheet value associated hyperlink. note create hyperlink first , outside of vba process. how did it:

a) open visual basic under developer tab

b) right-click workbook want add macro , click insert >module

c) copy , paste following code module:

 sub filter(scriteria 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 module tells workbook autofilter column named isometric number on hyperlinked tab particular value called out below. can change column name whatever want, needs changed in module , on tab want autofilter occur.

d) copy , paste following code main tab (expand microsoft excel objects , double click main tab - hyperlink , i'm filtering lives).

sub worksheet_followhyperlink(byval target hyperlink)    call filter(scriteria:=cells(target.parent.row, 2).value) end sub 

this code determines filter criteria module above auto filter by. right set filter value in cell in same row hyperlink , in 2nd column.

now question: when hyperlink refers separate workbook (instead of sheet within same workbook) following error : object variable or block variable not set (error 91). when run debug tool line 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 bit of reading on i'm having difficulties figuring out how read , understand code. closest thing answer has been post: can excel vba invoke autofilter when hyperlink clicked open new workbook?

your appreciated.

try changing line, referencing the newly-opened workbook. may need change 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 

Comments

Popular posts from this blog

account - Script error login visual studio DefaultLogin_PCore.js -

xcode - CocoaPod Storyboard error: -