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.

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

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

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.

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

On Wed, 6 Dec 2006 09:36:00 -0800, RedDevil
wrote:

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.

This works fantastic! Thanks! Is there a way to change "Cells.Clear" so
that it only clears a certain section instead of the whole sheet? We used
abbreviations for our sheet names but would like to have the longer name next
to the abbreviated hyperlink for people new to the abreviations.

I have modified it so that the hyperlinks appera in Column B, starting in
cell 3, can we have it clear only column B from 3+?

Thanks in advance.

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

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


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

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

Thank you,

Susan

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

"Susan" wrote in message
...
I am using this macro, but how can i add a step so that the macro
alphabetizes the sheet names?

Thank you,

Susan

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

Thank you so much!!!

"Don Guillett" wrote:

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

"Susan" wrote in message
...
I am using this macro, but how can i add a step so that the macro
alphabetizes the sheet names?

Thank you,

Susan

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

Glad to help

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Susan" wrote in message
...
Thank you so much!!!

"Don Guillett" wrote:

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

"Susan" wrote in message
...
I am using this macro, but how can i add a step so that the macro
alphabetizes the sheet names?

Thank you,

Susan

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