excel - VBA to create hyperlinks from a list -

-

i have below vba: 

sub list_creator() ' ' list_creator macro ' creates list of names become tab names '  ' sheets("all scheme derivatives").select activesheet.range("$a$1:$q$64944").autofilter field:=9, criteria1:=array( _     "a - mini", "b - supermini", "c - lower medium", "d - upper medium", _     "e - executive", "g - specialist sports", "h - mpv", "i - 4 x 4", "y - lcv", "="), _     operator:=xlfiltervalues columns("b:b").select selection.specialcells(xlcelltypevisible).select selection.copy sheets("list").select sheets("list").name = "list" range("a1").select selection.pastespecial paste:=xlpastevalues, operation:=xlnone, skipblanks _     :=false, transpose:=false application.cutcopymode = false activesheet.range("$a$1:$a$1047980").removeduplicates columns:=1, header:= _     xlno  dim ws worksheet dim ki range dim listsh range  worksheets("list")     set listsh = .range("a2:a" & .cells(.rows.count, "a").end(xlup).row) end  on error resume next each ki in listsh     if len(trim(ki.value)) > 0         if len(worksheets(ki.value).name) = 0             worksheets.add(after:=worksheets(worksheets.count)).name = ki.value          activesheet.[a1] = activesheet.name          'copy sheet helper         sheets("helper").range("a2:k92").copy destination:=activesheet.range("a2")         ' sets column widths         columns("b:c").columnwidth = 10.71         columns("d").columnwidth = 70.71         columns("e:j").columnwidth = 10.71         ' deletes rows aren't needed         dim lr long, found range         lr = range("c" & rows.count).end(xlup).row         set found = columns("c").find(what:="-", lookin:=xlvalues, lookat:=xlwhole)         if not found nothing rows(found.row & ":" & lr).delete         end if     end if next ki  ' return manual  sheets("manual").select end sub

this creates list of names (removing duplicates) , each name in list, new worksheet added workbook. these new worksheets have exact name appear in aforementioned created list. there way in create hyperlink each of these created worksheets on separate worksheet named "contents" (starting in cell l8, having 1 hyperlink per row).

thanks!

edit:

sub list_creator() ' ' list_creator macro ' creates list of names become tab names '  ' sheets("all scheme derivatives").select activesheet.range("$a$1:$q$64944").autofilter field:=9, criteria1:=array( _     "a - mini", "b - supermini", "c - lower medium", "d - upper medium", _     "e - executive", "g - specialist sports", "h - mpv", "i - 4 x 4", "y - lcv", "="), _     operator:=xlfiltervalues columns("b:b").select selection.specialcells(xlcelltypevisible).select selection.copy sheets("list").select sheets("list").name = "list" range("a1").select selection.pastespecial paste:=xlpastevalues, operation:=xlnone, skipblanks _     :=false, transpose:=false application.cutcopymode = false activesheet.range("$a$1:$a$1047980").removeduplicates columns:=1, header:= _     xlno   dim ws worksheet dim ki range dim listsh range dim ilinkrow integer   worksheets("list")     set listsh = .range("a2:a" & .cells(.rows.count, "a").end(xlup).row) end  on error resume next each ki in listsh     if len(trim(ki.value)) > 0         if len(worksheets(ki.value).name) = 0          worksheets.add(after:=worksheets(worksheets.count)).name = ki.value          activesheet.[a1] = activesheet.name          ilinkrow = 11          sheets("contents").hyperlinks.add anchor:=sheets("contents").cells(ilinkrow, 8), address:=activesheet.name, subaddress:=activesheet.name, texttodisplay:=activesheet.name          ilinkrow = ilinkrow + 1         'copy sheet helper         sheets("helper").range("a2:k92").copy destination:=activesheet.range("a2")         ' sets column widths         columns("b:c").columnwidth = 10.71         columns("d").columnwidth = 70.71         columns("e:j").columnwidth = 10.71         ' deletes rows aren't needed         dim lr long, found range         lr = range("c" & rows.count).end(xlup).row         set found = columns("c").find(what:="-", lookin:=xlvalues, lookat:=xlwhole)         if not found nothing rows(found.row & ":" & lr).delete         end if     end if next ki  ' return manual  sheets("manual").select end sub

you can add hyperlinks in workbook refer other sheets follows ...

activesheet.hyperlinks.add anchor:=selection, address:="", subaddress:= _     "sheet2!a1", texttodisplay:="sheet2!a1"

so instance, if had sheet called john use following add link cell l8 on contents sheet...

sheets("contents").hyperlinks.add anchor:=sheets("contents").range("l8"), address:="", subaddress:= _     "john!a1", texttodisplay:="john"

you should able put line of code similar (obviously without hard coding subaddress , texttodisplay parameters) in loop creates worksheets.

you need update anchor parameter. let's assume following loop

dim ilinkrow integer ilinkrow = 11 each ki in listsh     'your code creates sheet     sheets("contents").hyperlinks.add anchor:=sheets("contents").cells(ilinkrow, 8), address:="", subaddress:= _ activesheet.name, texttodisplay:=activesheet.name     ilinkrow = ilinkrow + 1 next ki

here, using cells(y,x) (rather range) accepts 2 integers row,column. column number 8 (l 8th column) , row (ilinkrow) increased 1 each sheet.

update code follows ...

on error resume next ilinkrow = 11 each ki in listsh if len(trim(ki.value)) > 0     if len(worksheets(ki.value).name) = 0      worksheets.add(after:=worksheets(worksheets.count)).name = ki.value      activesheet.[a1] = activesheet.name      sheets("contents").hyperlinks.add anchor:=sheets("contents").cells(ilinkrow, 8), address:=activesheet.name, subaddress:=activesheet.name, texttodisplay:=activesheet.name      ilinkrow = ilinkrow + 1

you need set ilinkrow = 11 before loop starts!


Comments

Post a Comment

Popular posts from this blog

Fail to load namespace Spring Security http://www.springframework.org/security/tags -

-
i have problem namespace of spring security on xhtml page. don't understand why can't load uri, , have following issue: whitelabel error page application has no explicit mapping /error, seeing fallback. tue jul 21 16:58:12 utc 2015 there unexpected error (type=not found, status=404). please need help! possible uri has been changed? best regards you using incorrect namespace url. correct url http://www.springframework.org/schema/security the error page seeing spring source website since not recognize url. try visiting http://www.springsource.org/security/tags in browser update : spring security tags used in jsp page correct tag lib declaration is: <%@ taglib prefix="sec" uri="http://www.springframework.org/security/tags" %>
Read more

sql - MySQL query optimization using coalesce -

-
can explain me why second query faster first? (first 1 takes around 40 seconds, second 1 less 0.1) select releases.feature_code, releases.platform_id, version, release_date, general_available, uri, filesize, display_name, feature_name.full_name, if ((select exists(select 1 release_exceptions releases.feature_code = release_exceptions.feature_code , releases.platform_id = release_exceptions.platform_id , releases.version = release_exceptions.version) = 1), 1, 0) release_exceptions releases left outer join platform using(platform_id) left outer join feature_name using(feature_code) order version desc; ~~~ query 2 ------------------ ~~~ select feature_code, version, release_date, general_available, platform_id, uri, filesize, display_name, feature_name.full_name, coalesce(release_exceptions, 0) release_exceptions mus.releases left outer join ( select distinct feature_code, version, platform_id, 1 release_exceptions mus.release_exceptions ) release_exceptions using ...
Read more

unity3d - Unity local avoidance in user created world -

-
i'm using unity3d networked multiplayer online game have large complex 3d terrain scene forest, trees, cliff, hills, mountains, bounders, etc. players can build structures sort of minecraft, , put them anywhere in scene, or move them around anytime. aside human controlled players, there automated ai players , objects animals roaming around scene following path. the problem how make these automated ai players , animals, able navigate around static , dynamic player created structures, because path follow can blocked player created structures, or other players , other ai objects, cliffs etc. have find away around them or on track if tumble down off high cliff example. so made navmesh , used navagents, takes care of static, non moving objects, how make ai players navigate around each other , dynamic structures created players can number in hundreds? i thought of adding navmeshobstacle everything, result in being attached hundreds of objects since user created structures b...
Read more