Organizing Tabs
Posted by Matt Evans on October 10, 2001 4:08 PM
I have a workbook with a lot of tabs that I'd like to organize so it's easier for others to navigate. The tabs can fall under a couple of different categories, so I'm wondering if there's a way to do some kind of "table of contents" worksheet, with a link that takes you to the tab you click on?
If not, how can I organize tabs, perhaps into some hierarchy?
Posted by Richard S on October 10, 2001 4:11 PM
www.asap-utilities.com
Try www.asap-utilities.com. One of the utilities enables you to create an index page, which has a hyperlink to each sheet in your worbook.
Richard
Posted by Jerid on October 11, 2001 8:18 AM
Try This
Matt, here is a procedure I wrote that will create a TOC sheet and add a hyperlink for each sheet in your workbook. Add the code to your ThisWorkbook module, run it whenever you add a new sheet. (the first thing it does is deletes the old TOC sheet)
Hope this helps, Jerid
Sub CreateTOC()
Dim CurSheet As Worksheet
Dim sSheetNames() As String
Dim iCounter As Integer
Dim iX As Integer
'If the workbook already has a TOC sheet, delete it
For Each CurSheet In Application.Worksheets
If CurSheet.Name = "TOC" Then
Application.DisplayAlerts = False
CurSheet.Delete
Application.DisplayAlerts = True
End If
Next CurSheet
iCounter = 0
'Put all sheet names in this workbook in an array
For Each CurSheet In Application.Worksheets
CurSheet.Activate
'Add one element to the array
ReDim Preserve sSheetNames(iCounter)
'Get the sheet name
sSheetNames(iCounter) = CurSheet.Name
'Increment the counter
iCounter = iCounter + 1
Next CurSheet
'Add new TOC sheet
Worksheets.Add Before:=Worksheets(1)
Application.ActiveSheet.Name = "TOC"
'Add Hyperlinks to the TOC sheet
For iX = 1 To UBound(sSheetNames)
Application.Range("A" & iX).Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:=sSheetNames(iX) & "!A1"
Next iX
Columns("A:A").EntireColumn.AutoFit
Application.Range("A1").Select
End Sub
