Sheet Index

L

Linda

Hello again,

Excel 2000 sp-1.

Is there a way to make an index for the different sheets in my workbook. I
have 50 pages and it's getting hard to remember the names of each sheet to
find it. I thought it would be nice to have an index with hyperlinks to
each sheet. How do I go about this or is there already a way to see my
sheets all at once without scrolling from side to side?

Thanks,
Linda
 
E

Earl Kiosterud

Linda,

Right-click on any of the four sheet scroll buttons (far left of the sheet
tabs).
 
B

Bob Phillips

Linda,

I believe Dave McRitchie has a web page on this at
http://www.mvps.org/dmcritchie/excel/buildtoc.htm.

You can also see a list of sheets by right-clicking on the sheet navigation
buttons to the left of the sheet name tabs. If you have 50 you won't see
them all at once, but you will get a more option .

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
D

Don Guillett

You can use something like this to create your list. This does it for col F
Sub sheetlist()
x = Cells(1, "f").End(xlUp).Row + 1
For Each sh In Sheets
If sh.Name <> "Sheet1" Then
Cells(x, "f") = sh.Name
x = x + 1
End If
Next
End Sub
==
Instead of a hyperlink, just use a double click event
right click on the sheet tab>view code>insert this>save workbook

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Application.DisplayAlerts = False
Dim WantedSheet As String
WantedSheet = Trim(ActiveCell.Value)
If WantedSheet = "" Then Exit Sub
On Error Resume Next
If Sheets(ActiveCell.Value) Is Nothing Then
GetWorkbook ' calls another macro to do that
Else
Sheets(ActiveCell.Value).Select
ActiveSheet.Range("a4").Select
End If
Application.DisplayAlerts = True
End Sub
 
T

Tom Ogilvy

right click on the VCR style arrows in the lower left corner of the window,
adjacent to the tab names.
 
L

Linda

Thanks, Everyone! The R click trick will be very helpful and will get us
through until we get to the other suggestions.

Linda
 
R

Ron de Bruin

Maybe you like this macro

Sub SheetList_CP()
'Chip Pearson, 2002-10-29, misc., %23ByZYZ3fCHA.1308%40tkmsftngp11
'Dave Peterson, same date/thread, 3DBF0BA8.4DAE9DA0%40msn.com
On Error Resume Next
Application.CommandBars("Workbook Tabs").Controls("More Sheets...").Execute
If Err.Number > 0 Then
Err.Clear
Application.CommandBars("Workbook Tabs").ShowPopup
End If
On Error GoTo 0
End Sub
 
L

Linda

Hi Ron,

I know what a macro is and I have done used a couple in word with step by
step instructions on where and how to insert it but it looks scary to me.

I copied and pasted it for a rainy day though. Thankyou!

Linda
 
Top