G'day all

I have been using Don Guillett's excellent code (copied below) to create a
automatic Table of Contents.

Is there any way to automatically include page numbers to the right of the
list of sheet names (obviously in its own cells)? Also I have information
which is to be manually entered against the list on the left of the list of
sheet names, so any code would have to take into account this as well.

Also I took out the sort function as I don't need it.

FYI - Don's code starts on A1 of the sheet. I wanted it to start on a
different cell so I made the following changes to make the list start at C3.

Cells(i, 1).Value = ms to Cells(3 + i, 3).Value = ms
If Target.Column < 1 Then Exit Sub to If Target.Column < 3 Then Exit Sub

Thanks to all.

Don Guillett's Code
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

If you want to put the relative tab number into the table of contents, not
the actual page number that it might be when printed, then try this:

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

For i = 1 To Sheets.Count
ms = Sheets(i).Name
If ms < ActiveSheet.Name Then
Cells(i + 3, 1).Value = ms
Cells(i + 3, 2).Value = i
End If
Next i
End Sub


"Art Vandelay" wrote:

G'day all

I have been using Don Guillett's excellent code (copied below) to create a
automatic Table of Contents.

Is there any way to automatically include page numbers to the right of the
list of sheet names (obviously in its own cells)? Also I have information
which is to be manually entered against the list on the left of the list of
sheet names, so any code would have to take into account this as well.

Also I took out the sort function as I don't need it.

FYI - Don's code starts on A1 of the sheet. I wanted it to start on a
different cell so I made the following changes to make the list start at C3.

Cells(i, 1).Value = ms to Cells(3 + i, 3).Value = ms
If Target.Column < 1 Then Exit Sub to If Target.Column < 3 Then Exit Sub

Thanks to all.

Don Guillett's Code
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

Thanks JLatham for the code, but unfortunately I'm after the page number.

For example:-
First tab is the Table of Contents
Second tab prints one page
Third tab prints 3 pages
Forth tab prints 2 pages

On the footer of each worksheet , i have put the "Page of Page" reference so
when you do a Entire Workbook print the pages are numbers 1 to 7 (in this
example).

So the result should look like:-

Table of Contents 1
Name of SecondTab 2
Name of Third tab 3
Name of Forth tab 6

So if you then swapped around, say, the third & forth tab the TOC would
automatically read;-

Table of Contents 1
Name of SecondTab 2
Name of Forth tab 3
Name of Third tab 5

Also, please remember i have something on the left of the list which is
manually entered but would need to move with the list. I suppose you would
call this my optional extra to the code. It's not critical but I would prefer
it and make life easier. The page numbering is the critical thing though.

Thanks again for yours (and everyone's) help.

"JLatham" wrote:

If you want to put the relative tab number into the table of contents, not
the actual page number that it might be when printed, then try this:

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

For i = 1 To Sheets.Count
ms = Sheets(i).Name
If ms < ActiveSheet.Name Then
Cells(i + 3, 1).Value = ms
Cells(i + 3, 2).Value = i
End If
Next i
End Sub


"Art Vandelay" wrote:

G'day all

I have been using Don Guillett's excellent code (copied below) to create a
automatic Table of Contents.

Is there any way to automatically include page numbers to the right of the
list of sheet names (obviously in its own cells)? Also I have information
which is to be manually entered against the list on the left of the list of
sheet names, so any code would have to take into account this as well.

Also I took out the sort function as I don't need it.

FYI - Don's code starts on A1 of the sheet. I wanted it to start on a
different cell so I made the following changes to make the list start at C3.

Cells(i, 1).Value = ms to Cells(3 + i, 3).Value = ms
If Target.Column < 1 Then Exit Sub to If Target.Column < 3 Then Exit Sub

Thanks to all.

Don Guillett's Code
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

Thanks JLatham for the code, but unfortunately I'm after the page number.

For example:-
First tab is the Table of Contents
Second tab prints one page
Third tab prints 3 pages
Forth tab prints 2 pages

On the footer of each worksheet , i have put the "Page of Page" reference so
when you do a Entire Workbook print the pages are numbers 1 to 7 (in this
example).

So the result should look like:-

Table of Contents 1
Name of SecondTab 2
Name of Third tab 3
Name of Forth tab 6

So if you then swapped around, say, the third & forth tab the TOC would
automatically read;-

Table of Contents 1
Name of SecondTab 2
Name of Forth tab 3
Name of Third tab 5

Also, please remember i have something on the left of the list which is
manually entered but would need to move with the list. I suppose you would
call this my optional extra to the code. It's not critical but I would prefer
it and make life easier. The page numbering is the critical thing though.

Thanks again for yours (and everyone's) help.

"JLatham" wrote:

If you want to put the relative tab number into the table of contents, not
the actual page number that it might be when printed, then try this:

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

For i = 1 To Sheets.Count
ms = Sheets(i).Name
If ms < ActiveSheet.Name Then
Cells(i + 3, 1).Value = ms
Cells(i + 3, 2).Value = i
End If
Next i
End Sub


"Art Vandelay" wrote:

G'day all

I have been using Don Guillett's excellent code (copied below) to create a
automatic Table of Contents.

Is there any way to automatically include page numbers to the right of the
list of sheet names (obviously in its own cells)? Also I have information
which is to be manually entered against the list on the left of the list of
sheet names, so any code would have to take into account this as well.

Also I took out the sort function as I don't need it.

FYI - Don's code starts on A1 of the sheet. I wanted it to start on a
different cell so I made the following changes to make the list start at C3.

Cells(i, 1).Value = ms to Cells(3 + i, 3).Value = ms
If Target.Column < 1 Then Exit Sub to If Target.Column < 3 Then Exit Sub

Thanks to all.

Don Guillett's Code
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