Excel Excel 40+ tabs need to link quickly

N

newscat21

Joined
Sep 5, 2012
Messages
1
Reaction score
0
I have created a "cover page" of sorts listing the names of the tabs. Since there are so many, it is difficult and time consuming to switch through them with keys or the mouse. Is there a way to make link on the cover page to take you directly to the tab?
 
You could create buttons with code behind them to go to the specified sheets. You can take a look here or try Google for the basics of that.
 
Here is code I used in the past, I know it works in '03 and '07. I am using '10 now and did not have any issues.
'---------------------------------------------------------------------------------------
' Procedure : CreateTableOfContents
' Author : Steven (stoneboysteve)
' Date : 12/30/2009
' Purpose : In the case there is no TOC it is created. If existing, it updates. (modified mrExcel.com)
'---------------------------------------------------------------------------------------
'
Sub CreateTableOfContents()
' Determine if there is already a Table of Contents
' Assume it is there, and if it is not, it will raise an error
' if the Err system variable is > 0, you know the sheet is not there
Dim WST As Worksheet
Dim TOCRow
Dim PageCount
Dim Msg

On Error Resume Next
'Set WST = Worksheets("Table of Contents")
Set WST = Worksheets("TOC")
If Not Err = 0 Then
' The Table of contents doesn't exist. Add it
Set WST = Worksheets.Add(Before:=Worksheets(1))
WST.Name = "TOC"
End If
On Error GoTo 0

' Set up the table of contents page
WST.[A1] = "Table of Contents"
With WST.[A3]
.CurrentRegion.Clear
.Value = "Sheet Name"
End With
WST.[B3] = "Printed Pages"
WST.Range("A1:B1").ColumnWidth = Array(36, 12)
TOCRow = 4
PageCount = 0
' Do a print preview on all sheets so Excel calcs page breaks
' The user must manually close the PrintPreview window
Msg = "Excel needs to do a print preview to calculate the number of pages. "
Msg = Msg & "Please dismiss the print preview by clicking close."
MsgBox Msg
ActiveWindow.SelectedSheets.PrintPreview
' Loop through each sheet, collecting TOC information
For Each S In Worksheets
If S.Visible = -1 Then
S.Select
' Use any one of the following 3 lines
ThisName = ActiveSheet.Name
'ThisName = Range("A1").Value
'ThisName = ActiveSheet.PageSetup.LeftHeader
HPages = ActiveSheet.HPageBreaks.Count + 1
VPages = ActiveSheet.VPageBreaks.Count + 1
ThisPages = HPages * VPages
' Enter info about this sheet on TOC
Sheets("TOC").Select
With Range("A" & TOCRow)
'Range("A" & TOCRow).Value = ThisName
.Value = ThisName
'Range("A" & TOCRow)

.Hyperlinks.Add Anchor:=Range("A" & TOCRow), Address:="#'" & ThisName & "'!A1", TextToDisplay:=ThisName

End With

Range("B" & TOCRow).NumberFormat = "@"
If ThisPages = 1 Then
Range("B" & TOCRow).Value = PageCount + 1 & " "
Else
Range("B" & TOCRow).Value = PageCount + 1 & " - " & PageCount + ThisPages
End If
PageCount = PageCount + ThisPages
TOCRow = TOCRow + 1
End If
Next S
End Sub
 
Back
Top