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

[Cosmos75]

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!

[Al Chara]

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 ]

[Russell Hauf]

...and if you want to learn something along the way, try this:

Option Explicit 



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 

Hope it helps,

Russell

[Cosmos75]

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.

[Russell Hauf]

It worked for me. Check the color of the text in those cells. You changed "Sheet1" to "Summary", right?

[Russell Hauf]

Try this instead:

Option Explicit 



Sub CreateHyperLinks() 

    

    Dim ws As Worksheet 

    

    For Each ws In Sheets 

    

    With Sheets("Sheet1").Cells(ws.Index, 1) 

        .Select 

        .Hyperlinks.Add Anchor:=Selection, Address:="", _ 

            SubAddress:=ws.Name & "!A1", TextToDisplay:=ws.Name 

    End With 



    Next ws 



End Sub 

[Cosmos75]

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 ]

[Russell Hauf]

Interesting. It worked without the single quotes for me (XL2000).

[Cosmos75]

Excel 2000 here as well... Hmm, interesting...?