Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,422
    Thanks
    125
    Thanked 5 Times in 5 Posts

    Macro to create index sheet

    I would like a macro to create an index sheet that updates when new sheets are added or deleted

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Howard,

    Here's one possible solution:
    Code:
    Option Explicit
    
    Private Sub Worksheet_Activate()
    
     Dim sht      As Worksheet
     Dim lCurRow  As Long
     
     lCurRow = 2
     
     For Each sht In ActiveWorkbook.Sheets
     
        If sht.Name <> "Index" Then
          Cells(lCurRow, 1).Select
          ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
            sht.Name & "!A1", TextToDisplay:=sht.Name
        lCurRow = lCurRow + 1
        End If
        
     Next sht
     
     '*** Clear out any deleted sheets ***
     Cells(lCurRow, 1).Select
     Range(Selection, Selection.End(xlDown)).Delete Shift:=xlUp
     
    End Sub
    Excel index.JPG
    Note that the code goes in the Index Sheet module. See attached sample file. The index is automatically recreated every time the Index sheet is selected.
    VBA - Excel - Create Index sheet.xlsm
    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. The Following User Says Thank You to RetiredGeek For This Useful Post:

    HowardC (2013-11-10)

  4. #3
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,422
    Thanks
    125
    Thanked 5 Times in 5 Posts
    Hi RG

    Thanks for the help

    Regards

    Howard

  5. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,640
    Thanks
    115
    Thanked 651 Times in 593 Posts
    Sweet RG!

    Assuming the Index sheet is the first sheet, here is another way to index the sheets but without the hyperlinks.

    Maud

    Code:
    Private Sub Worksheet_Activate()
         For I = 2 To Worksheets.Count:: Cells(I, 1) = Worksheets(I).Name:: Next I
    End Sub

  6. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Maud,

    Actually the code will work where ever the Index sheet is located as long as it is named Index. Of course the Index sheet will NOT show up in the Index. I has assumed (oh that nasty word ) the idea was to get to the sheets quickly thus the hyperlinks.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  7. #6
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,640
    Thanks
    115
    Thanked 651 Times in 593 Posts
    Assuming the Index sheet is the first sheet
    RG,

    I was referring to the code I posted. It must be in the Worksheet_Activate event subroutine of the first sheet, Worksheet(1), so that the code will display Worksheet(2) and Worksheet(3) as I cycles from 2 to the number of sheets.

    Your code is gold as always!

    Maud

  8. #7
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Quote Originally Posted by Maudibe View Post
    RG,
    Your code is gold as always!
    Maud
    Maud,

    Don't I WISH!
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  9. #8
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,422
    Thanks
    125
    Thanked 5 Times in 5 Posts
    Hi RG

    You recently helped me with code to create an index page with Hyperlinks. When I run the macro and click on the hyperlink, I get a message "Reference is not valid"

    I have attached my workbook. Please check & advise

    Howard
    Attached Files Attached Files

  10. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    When you have spaces in the sheet names you must include the single quote. Try changing the lines of code to

    ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
    "'" & sht.Name & "'!A1", TextToDisplay:=sht.Name

    Steve

  11. #10
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Steve,

    Thanks for updating this. I never put spaces in sheet names, a habit developed decades ago, so I keep forgetting about this requirement when I write code. I'll see if I can cram one more thing into this old cranium as it is always good practice to allow for all possibilities. Thanks again!
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  12. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    FYI, without the spaces the single quotes are NOT required but in cases like this it does NOT hurt to add them if not required. using them in INDIRECT , HYPERLINK. etc formulas around the sheet name will not hurt if they are not required (XL will remove them) but are neccessary when required and it is a good habit to just use them even if the names do not require them.

    Steve

  13. The Following User Says Thank You to sdckapr For This Useful Post:

    HowardC (2013-11-10)

  14. #12
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,422
    Thanks
    125
    Thanked 5 Times in 5 Posts
    Hi Steve

    I have added the code. I then clicked on the macro btton and then selected a hyperlink. I took me to tjhe required sheet. However, when I select the index sheet and click on any hyperlink, I get "reference is not valid"

    Please test & advise

    Howard
    Attached Files Attached Files

  15. #13
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    It needs to be added to both places in the code (there are 2 procedures that have that line...)

    Steve

  16. #14
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,422
    Thanks
    125
    Thanked 5 Times in 5 Posts
    Hi Steve

    Thanks, the macro works perfectly now

    Howard

  17. #15
    New Lounger
    Join Date
    Oct 2014
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by RetiredGeek View Post
    Howard,

    Here's one possible solution:
    Code:
    Option Explicit
    
    Private Sub Worksheet_Activate()
    
     Dim sht      As Worksheet
     Dim lCurRow  As Long
     
     lCurRow = 2
     
     For Each sht In ActiveWorkbook.Sheets
     
        If sht.Name <> "Index" Then
          Cells(lCurRow, 1).Select
          ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
            sht.Name & "!A1", TextToDisplay:=sht.Name
        lCurRow = lCurRow + 1
        End If
        
     Next sht
     
     '*** Clear out any deleted sheets ***
     Cells(lCurRow, 1).Select
     Range(Selection, Selection.End(xlDown)).Delete Shift:=xlUp
     
    End Sub
    Excel index.JPG
    Note that the code goes in the Index Sheet module. See attached sample file. The index is automatically recreated every time the Index sheet is selected.
    VBA - Excel - Create Index sheet.xlsm
    HTH
    Works great, just note that sheet names must not have spaces or dashes. Apparently, these special characters break the code.

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
  •