Hyperlink sheet name to its corresponding sheet

  • Thread starter Thread starter Abdul Shakeel
  • Start date Start date
A

Abdul Shakeel

Dear All

I got all sheets name in my workbook by this code
Sub GetSheetNames()

Dim i As Integer
Dim SheetName()
sheetcount = Application.Worksheets.Count
ReDim SheetName(1 To sheetcount)
For i = 1 To sheetcount
Cells(i, 1) = Sheets(i).Name
Next

End Sub

I want that when I got all the sheet names a code make hyperlink to its
corresponding sheet

Thanks in advance :)
 
Try this

Option Explicit

Sub GetSheetNames()

Dim WS As Excel.Worksheet
Dim aWS As Excel.Worksheet
Dim lRow As Long
lRow = 0
Set aWS = ThisWorkbook.ActiveSheet
For Each WS In ThisWorkbook.Worksheets
lRow = lRow + 1
aWS.Hyperlinks.Add Anchor:=aWS.Cells(lRow, 1), Address:="", SubAddress:= _
"'" & WS.Name & "'!A1", TextToDisplay:=WS.Name

Next WS

End Sub
 
Back
Top