Created list of worksheet names but need list to be hyperlinked to

[Guest]

Hello,

I have a workbook with 120 worksheets. I created an Index worksheet with
all of the names of each worksheet (A2-A121). Now I wish to somehow easily
hyperlink the index names to the worksheets. The worksheets and the index
are named exactly the same. Thanks for looking,

Mj

 

[JE McGimpsey]

Take a look here:

http://www.mvps.org/dmcritchie/excel/buildtoc.htm

 

[JW]

Here's a little VB routine I use to create a TOC sheet. HTH
Sub createTOC()
Dim ws As Worksheet, wsNw As Worksheet
Dim n As Integer
Set wsNw =
ActiveWorkbook.Worksheets.Add(Before:=ActiveWorkbook.Sheets(1))
With wsNw
starter:
On Error GoTo errHandler
.Name = "TOC"
On Error GoTo 0
.[a1] = "Table Of Contents"
.[a2] = ActiveWorkbook.Name & " Worksheets"
.[a1].Font.Size = 14
.[a2].Font.Size = 10
n = 4
For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> .Name And ws.Visible = True Then
.Cells(n, 1) = ws.Name
.Hyperlinks.Add _
Anchor:=.Cells(n, 1), _
Address:="", _
SubAddress:="'" & ws.Name & "'!A1"
n = n + 1
End If
Next
End With
Columns("A:A").EntireColumn.AutoFit
Exit Sub
errHandler: Application.DisplayAlerts = False
Sheets("TOC").Delete
Application.DisplayAlerts = True
GoTo starter
Error1:
MsgBox "No workbook open", vbCritical, "Error"
End Sub

 

[Guest]

Create a dropdrown object and attach a list to it using the range then on the
change event type:

Private Sub ComboBox1_Change()

WkName=Range(LinkedCell) ' The linked cell is the same you have selected on
the ComboBox Properties.
Worksheets(wkName).select

End Sub

 

[Guest]

Thank you. A lot of stuff to go through but the answer is within.

 

[Guest]

Thanks JW, exactly what I was looking for.
--
Thank You!

Here's a little VB routine I use to create a TOC sheet. HTH
Sub createTOC()
Dim ws As Worksheet, wsNw As Worksheet
Dim n As Integer
Set wsNw =
ActiveWorkbook.Worksheets.Add(Before:=ActiveWorkbook.Sheets(1))
With wsNw
starter:
On Error GoTo errHandler
.Name = "TOC"
On Error GoTo 0
.[a1] = "Table Of Contents"
.[a2] = ActiveWorkbook.Name & " Worksheets"
.[a1].Font.Size = 14
.[a2].Font.Size = 10
n = 4
For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> .Name And ws.Visible = True Then
.Cells(n, 1) = ws.Name
.Hyperlinks.Add _
Anchor:=.Cells(n, 1), _
Address:="", _
SubAddress:="'" & ws.Name & "'!A1"
n = n + 1
End If
Next
End With
Columns("A:A").EntireColumn.AutoFit
Exit Sub
errHandler: Application.DisplayAlerts = False
Sheets("TOC").Delete
Application.DisplayAlerts = True
GoTo starter
Error1:
MsgBox "No workbook open", vbCritical, "Error"
End Sub

Hello,

I have a workbook with 120 worksheets. I created an Index worksheet with
all of the names of each worksheet (A2-A121). Now I wish to somehow easily
hyperlink the index names to the worksheets. The worksheets and the index
are named exactly the same. Thanks for looking,

Mj

 

[Guest]

Thanks for looking Michael. JW's answer did the trick already.