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
--
Thank You!

Take a look he

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

In article ,
maijiuli wrote:

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

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(Befo=ActiveWorkbo ok.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

maijiuli wrote:
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
--
Thank You!

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




--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.




"maijiuli" wrote:

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
--
Thank You!

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


"JE McGimpsey" wrote:

Take a look he

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

In article ,
maijiuli wrote:

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

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


"JW" wrote:

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(Befo=ActiveWorkbo ok.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

maijiuli wrote:
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
--
Thank You!

Thanks for looking Michael. JW's answer did the trick already.
--
Thank You!


"Michael" wrote:

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




--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.




"maijiuli" wrote:

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
--
Thank You!