none
Macro to Create Index Linking to Worksheets RRS feed

  • Question

  • I have been trying for awhile now to create a macro that loops through worksheets in a workbook and creates and index linking to them on the first page, but adding the hyperlinks has been presenting some challenges. Does anyone have any suggestions? I am pretty new to VBA.

    Sub WorksheetLoop() 
    
    Dim WS_Count As Integer Dim I As Integer
    
    WS_Count = ActiveWorkbook.Worksheets.Count
    
    For I = 1 To WS_Count 
    ActiveSheet.Cells(I, 1).Value = ActiveWorkbook.Worksheets(I).Name
    
    ActiveSheet.Hyperlinks.Add _
    Anchor:=ActiveSheet.Cells(I, 1), _
    Address:="", _
    SubAddress:=ActiveWorkbook.Worksheets(I).Cells(1, 1), _
    TextToDisplay:=ActiveSheet.Cells(I, 1).Value 
    Next
    
    End Sub
    


    Tuesday, March 4, 2014 5:57 PM

All replies

  • Re:  adding hyperlinks

    Give this a try...
    (note the loop starts at 2)
    '---
    Sub WorksheetLoop_R1()
    Dim WS_Count As Integer
    Dim I As Integer

    WS_Count = ActiveWorkbook.Worksheets.Count
    For I = 2 To WS_Count
    Worksheets(1).Hyperlinks.Add Anchor:=Worksheets(1).Cells(I, 1), _
      Address:="", SubAddress:=Worksheets(I).Range("A1").Address(external:=True), _
      TextToDisplay:=Worksheets(I).Name
    Next
    End Sub
    '---
    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)

    • Edited by James Cone Sunday, October 30, 2016 7:42 PM
    Tuesday, March 4, 2014 7:41 PM
  • Try This:

    Sub Test()
    Dim i As Integer
    For i = 1 To Worksheets.Count
        Range("A" + Strings.Trim(Str(i))).Select
        ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", _
        SubAddress:=Worksheets.Item(i).Name + "!A1", _
        TextToDisplay:=Worksheets.Item(i).Name + "!A1"
    Next i
    End Sub


    VBA Programming Service

    • Edited by Pedrumj2 Tuesday, March 4, 2014 8:32 PM
    Tuesday, March 4, 2014 8:17 PM