How do I insert a hyperlink for a worksheet in a workbook?

Cosmos75

Active Member
Joined
Feb 28, 2002
Messages
359
How do I insert a hyperlink for a worksheet in a workbook?

I have a macro that creates a summary page with all names of the workbook on this summary page. I’d like to add code that would link the cells with the workbook names to the respective worksheets.

Here’s what I have so far.

For Each ws In Sheets
x = x + 1
'x + 1 makes it go to the next row to paste ws.name
Sheets("Summary").Range("A" & x).Value = ws.Name

Next ws

Thanks!
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Forget what you have done and download ASAP:
http://asap-utilities.com/
It has a command that will "Create an index page with all sheets (clickable)" That should do the trick for you. Everyone should download this program; I have found it so useful.

_________________
Hope this helps.
Kind regards, Al.
This message was edited by Al Chara on 2002-04-10 12:42
 
Upvote 0
...and if you want to learn something along the way, try this:

<pre><font color='#000000'>
<font color='#000080'>Option</font> <font color='#000080'>Explicit</font>

<font color='#000080'>Sub</font> CreateHyperLinks()

<font color='#000080'>Dim</font> ws <font color='#000080'>As</font> Worksheet

<font color='#000080'>For</font> <font color='#000080'>Each</font> ws In Sheets

With Sheets("Sheet1").Cells(ws.Index, 1)
.Select
.Value = ws.Name
.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:=ws.Name & "!A1"
<font color='#000080'>End</font> With

<font color='#000080'>Next</font> ws

<font color='#000080'>End</font> <font color='#000080'>Sub</font>

</font></pre>

Hope it helps,

Russell
 
Upvote 0
Al,

Thanks, but I think the IT people here would frown on my installing an add-in.

Russell,

Thanks, but it doesn't work. I did an edit Hyperlink and the links are there as Cell References but the hyperlinked cells are empty.
 
Upvote 0
It worked for me. Check the color of the text in those cells. You changed "Sheet1" to "Summary", right?
 
Upvote 0
Try this instead:

<pre><font color='#000000'>
<font color='#000080'>Option</font> <font color='#000080'>Explicit</font>

<font color='#000080'>Sub</font> CreateHyperLinks()

<font color='#000080'>Dim</font> ws <font color='#000080'>As</font> Worksheet

<font color='#000080'>For</font> <font color='#000080'>Each</font> ws In Sheets

With Sheets("Sheet1").Cells(ws.Index, 1)
.Select
.Hyperlinks.Add Anchor:=Selection, Address:="", _
SubAddress:=ws.Name & "!A1", TextToDisplay:=ws.Name
<font color='#000080'>End</font> With

<font color='#000080'>Next</font> ws

<font color='#000080'>End</font> <font color='#000080'>Sub</font>

</font></pre>
 
Upvote 0
Got it to work. Had to change this.

.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:=ws.Name & "!A1"

To this.

.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:="'" & ws.Name & "'!A1"

EDIT: That SubAddress:="'" & ws.Name & "'!A1" is hard to make out but it's SubAddress:=" ' " & ws.Name & " ' !A1"

I didn't include the Option Explicit before Sub CreateHyperLinks(), does that make a difference. What does Option Explicit do?

Sub CreateHyperLinks()

Dim ws As Worksheet

For Each ws In Sheets

With Sheets("Sheet1").Cells(ws.Index, 1)
.Select
.Value = ws.Name
.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:="'" & ws.Name & "'!A1"
End With

Next ws

End Sub
This message was edited by Cosmos75 on 2002-04-10 13:43
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,177
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top