Hyperlink Questions

[David T]

I have a macro that adds a new named tab for each name a list range. I need
to add a code to my macro that will add a hyperlink next to each name in the
range which will take me directly to the page. My current code is below.
Can anyone help? Below is the macro. Can anyone add to it?

Sub name_sheets()
'will add a sheet, and name it
'for each name in column A
'from A1 down till it hits a blank row
Dim Rng As Range
Dim ListRng As Range

Set ListRng = Range(Range("C6"), Range("C6").End(xlDown))


For Each Rng In ListRng
If Rng.Text <> "" And Rng.EntireRow.Hidden = False And Rng.Text <> "Buy
/ Sale" _
And Rng.Text <> "Buy/Sale" Then
With Worksheets
.Add(after:=.Item(.Count)).Name = Rng.Text

End With


End If
Next Rng
End Sub

 

[Chad]

try something like this in your code:
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
"Sheet2!A1", TextToDisplay:="Sheet2!A1"

 

[David T]

Hey Chad...it's adding a hyperlink to all the worksheets, but not the index
page.

 

[Chad]

I'm going to need a little more information on this to provide more info on
your code and incorporating this addition of a hyperlink. thanks.

 

[David T]

Hey Chad-

i have a list of states in column "B" with different cities related to the
names in column "C". I can filter the list by the different states. My
macro (at the bottom of the email) creates a new worksheet for each city .
I need my macro to add a hyperlink to each contract # (in column D) which
will take me directly to the sheet which was created for the city

I thank you in advance for your help

Column B Column C Column D
Names Cities Hyperlink

New York Albany (need hyper link to take me to WS "Albany")
New York New York
New York Long Island
New York Manhattan
Texas Houston
Texas Austin
Texas San Antonio

 

[Chad]

based upon the data you provided, try something like this after you create
the sheet for the city:

'make sure you are on the index worksheet with all the cities
linkname = Range("C2").Value 'city name
linkrange = linkname & "!A1" 'link to city worksheet
Range("D2").Select 'where you want the link to be
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
linkrange, TextToDisplay:=linkname


let me know how this works!
-chad

 

[David T]

hey chad

Thanks for your help. It works just fine

 

[Chad]

thanks for the feedback. glad it worked.

hey chad

Thanks for your help. It works just fine