MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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