how can I make a table of worksheet names that updates automatical

N

nah

I have a file with 20+ worksheets that keeps increasing in size. They are
not, unfortunately, in any alphabetical order. I'd like a table of contents
page that I can sort by alpha, if necessary, that will update itself as new
pages are added. I am using Excel 2003.
 
J

JLatham

Copy and paste this code into the _Activate() event for the worksheet that
you want to be the Table of Contents sheet:

Private Sub Worksheet_Activate()
Dim anySheet As Worksheet
Dim myCounter As Long

Cells.Clear ' remove previous content
myCounter = 0
For Each anySheet In Worksheets
If anySheet.Name <> ActiveSheet.Name Then
Range("A1").Offset(myCounter, 0) = anySheet.Name
'this adds hyperlink remove if you don't want it
ActiveSheet.Hyperlinks.Add anchor:=Range("A1").Offset(myCounter, 0), _
Address:="", SubAddress:=anySheet.Name & "!A1"
'update the offset counter, keep this
myCounter = myCounter + 1
End If
Next
End Sub

I took the liberty of setting up a hyperlink in each one to take you right
to that sheet if you click on one of them. Instructions on this page will
show you how to get to the place you need to be to add the code to the
Worksheet events section.
http://www.jlathamsite.com/Teach/WorksheetCode.htm

Each time you choose this worksheet the list will be updated to reflect all
current sheets in the workbook except itself.

Since you didn't ask for automatic sorting, it's not there. To use normal
sort on the list, it's kind of tricky with cells with hyperlinks: click in
one of the cells (top or bottom) and hold the mousebutton down for a short
time and then drag to include the whole list and sort. You have to click and
hold to select such a cell instead of just clicking, which activates the
hyperlink immediately. If you don't want the hyperlinks, just leave out that
one instruction (on 2 lines) and no hyperlink will be created.
 
N

nah

thanks. I get the list, but the links do not work. "reference not valid". THe
hyperlink is not necessary, but would be nice. I can sort the list by
selecting the column and sorting it.
 
J

JLatham

I'll look and try to see what might be wrong with the hyperlink setup. Could
be that some of your sheets have a space in their names?
 
J

JLatham

Make sure your sheet names don't contain the # symbol, that'll mess up
hyperlinks also. Most likely problem right now is that there's a space in
one or more sheet names, this would fix that, just replace the SubAddress:=
portion with this:

SubAddress:="'" & anySheet.Name & "'!A1"
if that's hard to read here, that is double-quote, single-quote,
double-quote in the first piece, then I added a single-quote in front of the
exclamation point in the trailing portion, so as to creat an entry similar to
this 'Sheet Name'!A1
 
N

nah

Thanks. I probably should have thought about the spaces, we have come up
against that problem with hyperlinks in the past on stuff I have tested, but
I wasn’t' thinking. At first it wasn't working, but for some reason now it
is. I can still sort by the column, although I need to re-do it each time.
But that's fine, it is still much faster than searching manually through 46
(at this point) sheets blindly to see if I have a particular one or not.
 
G

Gord Dibben

Try this macro.

Private Sub ListSheets()
'list of sheet names starting at A1
Dim Rng As Range
Dim i As Integer
Set Rng = Range("A1")
For Each Sheet In ActiveWorkbook.Sheets
Rng.Offset(i, 0).Value = Sheet.Name
i = i + 1
Next Sheet
End Sub


Gord Dibben MS Excel MVP
 
B

bai_AK_apo

I'm another fan of your helpfulness and code. Thought I had it, but it will
not pull in more than 41 sheets. Any ideas.
 
S

Susan

I am using this macro, but how can i add a step so that the macro
alphabetizes the sheet names?

Thank you,

Susan
 
D

Don Guillett

I wouldn't bother with hyperlinks. Copy both macros into your worksheet
module
Now, when you activate the list will be updated and sorted.
Then just double click on the cell to go the sheet desired

Private Sub Worksheet_Activate()
Dim i As Long
Dim ms As String

For i = 1 To Sheets.Count
ms = Sheets(i).Name
'MsgBox ms
If ms <> ActiveSheet.Name Then
Cells(i, 1).Value = ms
End If
Next i
'sorts list
Columns("a").sort key1:=Cells(2, 1)
End Sub
'=======
Private Sub Worksheet_BeforeDoubleClick _
(ByVal Target As Range, Cancel As Boolean)
If Target.Column <> 1 Then Exit Sub
Application.DisplayAlerts = False
Dim WantedSheet As String
WantedSheet = Trim(ActiveCell.Value)
If WantedSheet = "" Then Exit Sub
On Error Resume Next
If Sheets(WantedSheet) Is Nothing Then
'GetWorkbook ' calls another macro to do that
Else
Application.GoTo Sheets(WantedSheet).Range("a4")
End If
Application.DisplayAlerts = True
End Sub
 
S

Susan

Thank you so much!!!

Don Guillett said:
I wouldn't bother with hyperlinks. Copy both macros into your worksheet
module
Now, when you activate the list will be updated and sorted.
Then just double click on the cell to go the sheet desired

Private Sub Worksheet_Activate()
Dim i As Long
Dim ms As String

For i = 1 To Sheets.Count
ms = Sheets(i).Name
'MsgBox ms
If ms <> ActiveSheet.Name Then
Cells(i, 1).Value = ms
End If
Next i
'sorts list
Columns("a").sort key1:=Cells(2, 1)
End Sub
'=======
Private Sub Worksheet_BeforeDoubleClick _
(ByVal Target As Range, Cancel As Boolean)
If Target.Column <> 1 Then Exit Sub
Application.DisplayAlerts = False
Dim WantedSheet As String
WantedSheet = Trim(ActiveCell.Value)
If WantedSheet = "" Then Exit Sub
On Error Resume Next
If Sheets(WantedSheet) Is Nothing Then
'GetWorkbook ' calls another macro to do that
Else
Application.GoTo Sheets(WantedSheet).Range("a4")
End If
Application.DisplayAlerts = True
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
[email protected]
Click to expand...
 
Top