Create links from sheet names

Tested in Excel 365 (16.8730.2046) 64-bit

Useful if you want to create a Home or Content tab.

 
 
  1. Public Sub CreateSheetNameLinks()
  2. '===================================================================================
  3. ' Created by: Sanda Vladescu
  4. ' Updated on:
  5. ' Purpose: create a table of contents with hyperlink to each sheet
  6. ' References:
  7. ' Usage example:
  8. '
  9. '===================================================================================
  10.    Dim i As Integer
  11.    
  12.     If SheetExists("Summary") Then Sheets("Summary").Delete
  13.     Sheets.Add before:=Sheets(1)
  14.     ActiveSheet.Name = "Summary"
  15.     Sheets("Summary").Select
  16.    
  17.     For i = 2 To Sheets.Count
  18.         Range("B" & i).Select
  19.         ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
  20.         Sheets(i).Name & "!A1", TextToDisplay:= _
  21.         Sheets(i).Name
  22.     Next i
  23. End Sub

SheetExists function is here.

Add a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.