Hello,

I've just used this code and it works great! thank you! I just have one
question though. Is it exclusively for data sheets or will it pick up chart
sheets?

I have a huge workbook of pivots and pivot charts, this code works on the
pivot and data sheets, however, it's not picking up the pivot charts
worksheets.

could you help please?

Many thanks and much appreciated

TL

"JLatham" wrote:

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.