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

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.

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.


"nah" wrote:

> 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.

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.

"JLatham" wrote:

> 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.
>
>
> "nah" wrote:
>
> > 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.

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?

"nah" wrote:

> 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.
>
> "JLatham" wrote:
>
> > 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.
> >
> >
> > "nah" wrote:
> >
> > > 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.

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


"nah" wrote:

> 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.
>
> "JLatham" wrote:
>
> > 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.
> >
> >
> > "nah" wrote:
> >
> > > 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.