Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Create multiple hyperlinks (2002)

    Hi,
    I have a sheet with name's in F9:F170. Each name has another sheet. How can I:

    1. Create hyperlinks to the 162 sheets.
    2. Sort the 162 tabs into alphabetical order.
    3. Create a macro on all 162 sheets as a return to master sheet.
    Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Create multiple hyperlinks (2002)

    For #2: If you want to sort them alphabetically according to their names, see (for example) <post#=708,273>post 708,273</post#> for a macro that does that.

    Sorting And Ordering Worksheets on Chip Pearson's website has more options.

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Create multiple hyperlinks (2002)

    For #3: you could create a hyperlink to the master sheet, using a macro similar to the one from my previous reply (Post: 730,890]).

  4. #4
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Create multiple hyperlinks (2002)

    For #2

    Create a worksheet called SheetNames and hide it

    <pre>Sub SortSheets()
    Dim i As Integer, ii As Integer
    Dim ShtName As String
    If ActiveWorkbook.Sheets.Count > 1 Then
    Application.ScreenUpdating = False
    With ThisWorkbook.Sheets("SheetNames")
    .Columns(20).Clear

    For i = 1 To ActiveWorkbook.Sheets.Count
    .Cells(i, 20) = Sheets(i).Name
    Next

    .Columns(20).Sort Key1:=.Cells(1, 20), Order1:=xlAscending

    For ii = i - 1 To 1 Step -1
    ShtName = .Cells(ii, 20)
    ActiveWorkbook.Sheets(ShtName).Move Before:=ActiveWorkbook.Sheets(1)
    Next

    End With
    Application.ScreenUpdating = True
    End If
    End Sub
    </pre>

    Jerry

  5. #5
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create multiple hyperlinks (2002)

    Thanks both, 1&2 are both sorted. I'm a little stuck on 3 though. The workbook contains other sheets as well as the ones that I want to affect, so I'm thinking of running a macro to add a hyperlink back to the master sheet in cell A1 on (maybe) the selected sheets? ie, select the 162, then run macro?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Create multiple hyperlinks (2002)

    For #1:
    <code>
    Sub CreateHyperlinks()
    Dim r As Long
    Dim strSheet As String
    For r = 9 To 170
    strSheet = Range("F" & r)
    ActiveSheet.Hyperlinks.Add Anchor:=Range("F" & r), _
    Address:="", SubAddress:="'" & strSheet & "'!A1", _
    TextToDisplay:=strSheet
    Next r
    End Sub</code>

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Create multiple hyperlinks (2002)

    Try this; you should replace "Master" with the actual name of the master sheet.
    <code>
    Sub CreateHyperlinks2()
    Dim r As Long
    Dim strSheet As String
    For r = 9 To 170
    strSheet = Worksheets("Master").Range("F" & r)
    Worksheets(strSheet).Hyperlinks.Add Anchor:=Worksheets(strSheet).Range("A1"), _
    Address:="", SubAddress:="'Master'!A1", _
    TextToDisplay:="Return to master sheet"
    Next r
    End Sub</code>

  8. #8
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Create multiple hyperlinks (2002)

    <P ID="edit" class=small>(Edited by Jezza on 01-Sep-08 22:02. Forget this question, Hans posted while I was typing)</P>Hi Nathan

    Will the other sheets be at the beginning(from the left) or at the end of the stack of worksheets?
    Jerry

  9. #9
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create multiple hyperlinks (2002)

    Hans, As always, I appreciate your help. I am getting a 'subscript out of range error' and I can't see the fault.
    Attached Images Attached Images

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Create multiple hyperlinks (2002)

    What is the value of strSheet when the error occurs?

  11. #11
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create multiple hyperlinks (2002)

    <img src=/S/question.gif border=0 alt=question width=15 height=15> I apologise for my ditzyness, but I don't understand your question?

    Runtime Error 9...... (But I don't think that's the answer)

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Create multiple hyperlinks (2002)

    When the error message pops up, click Debug.
    The Visual Basic Editor will be activated, with the offending line highlighted (as in your screenshot).
    Hover the mouse pointer over one of the occurrences of the word strSheet. You should see its value.
    Alternatively, activate the Immediate window (Ctrl+G), then type
    <code>
    ? strSheet
    </code>
    and press Enter. What is the result?

  13. #13
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create multiple hyperlinks (2002)

    It's a name of one of the 162 sheets, but that does not make sense because the macro stipulates >> strSheet = Worksheets("Standings")

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Create multiple hyperlinks (2002)

    Nope, the macro has

    strSheet = Worksheets("Master").Range("F" & r)

    This means that strSheet is assigned the value of a cell in column F on the Standings sheet. Are you ABSOLUTELY sure that the name that you see is spelled correctly?

  15. #15
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create multiple hyperlinks (2002)

    There were a few differences between the tab names and the names that we created hyperlinks for on the standings sheet. Once corrected, the macro run perfectly. Many Thanks.

Page 1 of 2 12 LastLast

Posting Permissions

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