Quick Question on Hyperlinks

[rickey24]

Hi All

I am creating a bunch of new sheets in a workbook and on my summar
sheet want a cell to hyperlink to that sheet. Can't quite figure ou
how to get my variable to be valid in the subaddress line and point t
the newly created sheet and right cell

I Dim tsht as Worksheet and
Set tsht = Activesheet

My anchor:=Selection
Address:=""
Subaddress:=tsht
screentip:="Click here to go to "& hyper
TexttoDisplay:=hyper

I also tried using Hyper in the subaddress which is dim a string, an
has the value of the renamed sheet.

The error I get is reference is not Valid on the excel sheet.

Any help is greatly appreciated. Thanks.

R

 

[Dave Peterson]

I recorded a macro when I did it manually and got this:

ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
"Sheet4!C9", TextToDisplay:="Sheet4!C9"

So maybe:

dim tsht as worksheet
dim myHyper as string
set tSht = activesheet

myhyper = "'" & tsht.name & "'!c9"

ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
myhyper, TextToDisplay:=myhyper

 

[rickey24]

Thanks Dave, works great. I was never putting all the right quotes i
the right spots when assigning the value to the variable. Thanks s
much for the help.

R

 

[rickey24]

Dave

One last question, if I am using it in a: for i = 1 to lastrow an
have this statement in there:

myhyper = "'" & tsht.name & "'!c9"

I want the hyperlink to be Column C but Row i, how would you change th
statement? Again I can't seem to get my variables working on this.
Thanks again.

R

 

[Dave Peterson]

how about:

myhyper = "'" & tsht.name & "'!c" & i

 

[rickey24]

Awesome, thanks