Hyperlink sheet name to its corresponding sheet

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 :)
 
B

Barb Reinhardt

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top