Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

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

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    359
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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!

  2. #2
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  3. #3
    MrExcel MVP Russell Hauf's Avatar
    Join Date
    Feb 2002
    Location
    Portland, OR Area - USA
    Posts
    1,607
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    ...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

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    359
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  5. #5
    MrExcel MVP Russell Hauf's Avatar
    Join Date
    Feb 2002
    Location
    Portland, OR Area - USA
    Posts
    1,607
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  6. #6
    MrExcel MVP Russell Hauf's Avatar
    Join Date
    Feb 2002
    Location
    Portland, OR Area - USA
    Posts
    1,607
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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


  7. #7
    Board Regular
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    359
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  8. #8
    MrExcel MVP Russell Hauf's Avatar
    Join Date
    Feb 2002
    Location
    Portland, OR Area - USA
    Posts
    1,607
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  9. #9
    Board Regular
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    359
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •