Table of Contents with Page Numbers

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

0
Utf
12/3/2009 12:31:02 AM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
644 Views

Similar Articles

[PageSpeed] 46

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
> 
0
Utf
12/3/2009 1:11:01 PM
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
> > 
0
Utf
12/3/2009 11:06:01 PM
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
> > 
0
Utf
12/3/2009 11:06:01 PM
Reply:

Similar Artilces:

Payroll Tax table update
Does anyone know when the new payroll tax tables automatic updates will be ready for download, Does this include the new rates for the new stimulus plan? Thank You ...

cell comments in a vlookup table
i have information in a vlookup table that i only want to veiw when required.It cannot be done with cell coments as these do not come over within a lookup table.I thought of another column within the lookup table,but these are always visible, and i am running this program on a P.D.A and space is at a premium. ...

PiVot Table Formatting
I have a PiVot Table like this: Product ID Factory Sales 100 A 500 B 500 S 200 100 Total 1200 101 A 500 B 500 S 200 101 Total 1200 I want the the raws with total are in bold font. How can I do it? Thanks in advance. To enable selection -- On the Pivot toolbar, choose PivotTable>Select Click on Enable Selection To format the subtotals -- Move the pointer to the left edge of a subtotal heading in the pivot table. When the black arrow appears (like the one that appears when the pointer is over a row button), click to select all the subtotal rows for that field in the pivot table...

Pivot Table Madness
Geesh. I can't seem to get this... I have this table not reliably sorted: Job Number Employee Week1 Week2 Week3 0001 Fred 4 4 4 0001 Barney 8 8 8 0002 Wilma 4 4 4 0003 Betty 4 4 4 0001 BamBam 8 8 8 And I need this: Job Number Employee Week1 Week2 Week3 0001 Fred 4 4 4 Barney 8 8 8 BamBam 8 8 8 Total 20 20 20 0002 Wilma 4 4 4 Total 4 4 4 0003 Betty 4 4 4 Total 4 4 4 Basically I need my table grouped by job number thensorted by employee showing the hours assigned per week. Is this a job for pivot tables? How? thanks, -mjm If your data looked more lik...

Adding shadows to tables or objects
I have a table and want to add a shadow. Would think this would be fairly simple, but have not yet found the key! I have tried the 3-D toolbar, but it is disabled. not sure how to enable it. Thanks! To add a shadow to a table is more or less hit and miss. Select the table, fill the cells white, draw a rectangle the size of the table, color it, send it to the back, while the rectangle is selected, open the shadow dialogue and adjust. Most simple objects can have a shadow inserted. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com ...

mail merge ticket numbers
Here is one for all the guru's out there... I have created a set of 3 "tickets" on a single page in MS Word. I have created a list in Excel from 101-201. How can use the mail merge in MS Word to show the sequence ticket numbers? Example: Page 1 Ticket # 101 Ticket # 102 Ticket # 103 Page 2 Ticket # 104 Ticket # 105 Ticket # 106 And so on... Thanks, Tustin -- onesidered ------------------------------------------------------------------------ onesidered's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=25977 View this thre...

Macro for user to input 10 numbers
Hi, I'm a bit of a novice when it comes to macros and hoping someone can help. I need a user to be prompted (by a pop up box) to enter 10 numbers between 1-100, there are no duplicates allowed. these 10 numbers then need to be sorted and arranged in ascending order and displayed in cells B14:J14. I've managed to get an input box for one number but really struggling with the rest. Can anyone help? Sub numbers() Dim i As Long Dim n For i = 2 To 10 With Cells(14, i) Do n = Application.InputBox("Input number #" & i - 1, Type:=...

Create Item Number from Web Services Question
Hi All, I am trying to create a new item number in GP so that it's existed before I can create salesorder and salesitem but I don't know which method/methods from GP Web Services to do so. I found that there are CreateSalesOrder, CreateSalesItem, GetItemByKey but I could not find where CreateItem? Anyone has ideas, please let me know and I'd appreciate -Toi Use the CreateSalesItem method. The name is not as intuitive as it should be, but it's the method you need for a new item. You can find more info in the Web Services SDK. Best regards, -- MG.- Mariano Gomez, MIS,...

Sorting Text Fields Containing Letters and Numbers
To whom it may confuse. I have a query with a text field that needs to be sorted in descending order. The field name is "ContainerNumber" and the data will always have at least a letter in the begining. As you can see the data sample "D100/07" comes after "D10/07" and that is not what I need. I need "D11/07" to come after "D10/07" and so on. How can I fix this? ContainerNumber D03/07 D06/07 D07/07 D08/07 D09/07 D10/07 D100/07 D101/07 D102/07 D103/07 D104/07 D105/07 D106/07 D107/07 D108/07 D109/07 D11/07 D110/07 D111/07 D112/07 D113/07 D114...

How to add the number in cell one by one in Hex format
Dear all, Now, if I type in 01/0001 in cell, and then I drag the mouse down to many cells , the number in the cell will be added by 1 automatically, just like the following 01/0001 01/0002 01/0003 . . . Now, if I type in 01/0001 and I want to drag the mouse down to many cells and I want the cell to add 1 automatically in Hex format. Can I do that? Simply, I want the cell to add 1 automatically in Hex format. Just like that 01/0001 . . 01/0...

transitional slides to look like turning book pages?
any idea how to make transitional slides look like the turning pages of a book? http://www.technologytrish.co.uk/ppttipspageturn.html http://www.powerpointmagician.com/articles/3Dtransitions.htm http://office.microsoft.com/en-us/templates/TC103382671033.aspx?CategoryID=CT103366151033 - - one of the slides has a book with page turn animation http://www.crystalgraphics.com/presentations/transitions.main.asp -- Echo [MS PPT MVP] http://www.echosvoice.com What's new in PPT 2010? http://www.echosvoice.com/2010.htm Fixing PowerPoint Annoyances http://tinyurl.com/36grcd Power...

Pivot table totals #2
I have table that summarises sales / returns quanties. The figures are not signed so I used a calculated field to give a signed quantity. The data are figues are correct, the colums totals are correct but the row totals add up all values ignoring the sign. Am i doing something wrong or is this a problem in Excel? ...

Using Table Views in Smartlist Builder
Hi all, I'm using GP 8.0 I'd like to create a smartlist using table views however I can't get it work. When I select Views (instead of Tables) under the Add SQL Table window, there is nothing in there. Any help would be appreciated. TIA, Sugih Hi all, I've got the answer. Shouldn't use views at the first place. MGP provide Match Table to combine similar table in one view. Sugih "Sugih" wrote: > Hi all, > > I'm using GP 8.0 > I'd like to create a smartlist using table views however I can't get it work. > When I select Views (inst...

Can I filter a table on a time criteria?
I have a very large table of data in 10 minute intervals ie, 1:10:00, 1:20:00, etc. I would like to sort it to only the values (rows) for the top of the hour, ie. 1:00:00, 2:00:00. I tried an advanced filter listing out the 24 hour criteria. However it returned everything. Another possibility is if I sort the data by time the data that I want is in every 7th row. Is there a way to sort by row? or create a formula that I could drag down to give me every 7th row? Something like =(A2). Then =row(A8) If I highlight these two cells and drag them down the results are A3 and A9 when I ...

Numbers to Letters
I have a spreadsheet which autonumbers column A. However I would like a formula or macro to convert this so that 1=A, 2=B etc right up to 11111 = AAAAA, 11112 = AAAAB. Many Thanks in advance. Try this: Sub Autonumber() For Each Cell In Range("autonumber_range").Cells Newcode = "" For char = 1 To Len(Cell.Value) Newcode = Newcode & Chr(Asc(Mid(Cell.Value, char, 1)) + 16) Next Cell.Value = Newcode Next End Sub Select the range you want to convert, then run the macro. - Aidey Sorry - I meant to say "name the range you want to convert as ...

Using TAB key in a table
Hello folks I am sure that this would of been asked before but I cannot find any reference to it. Please can you tell me wether it is possible to use the TAB key within a single cell in a table. (not to advance to the next cell, but just to be able to line up text in another cell.) Maybe it is a key combination ctrl/tab for example. Your help is and always has been very much appreciated. Thanking you in anticipation. -- Big Rick Create a text box the size of the cell. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Big Ri...

How do I get a whole picture to appear between a two page spread .
I am trying to put pictures between two page spreads in publisher, but I don't want to lose 2 pica of the picture to the gutter. Is there anyway to have the picture split between the gutter without losing any of my picture or text. I think you will need to be a bit creative on the assembly of your booklet. If you want to make the picture as a "centerfold" it wouldn't be too difficult. Just make a separate file with a full sheet as the work area. Print it and then insert it in the proper sequence of paper to print the entire booklet. But in the main file, make the pages...

Getting Difference between values of 2 tables
Info: I have the following table that stores the total amount available for a particular Document: tblTotalAvail: ID CLIN Amount 1 0001 10,000 2 0002 30,000 3 0003 25,000 Etc... You get the idea. I have the following table that stores the expenditure for for the same document: tblExpenditures: ID DateFunded CLIN Amount 1 21-Jan-07 0001 2,500 2 30-Jan-07 0001 3,700 3 03-Feb-07 0002 11,000 4 14-Feb-07 0001 1,200 5 17-Feb-07 0002 7,500 Etc.... Here is what I am trying to do....I want to Sum the amount...

Inbox Showing Incorrect Number of Unread Messages
I'm having an issue with a user's mailbox - Outlook 2003 or 2007, Exchange 2003, Windows XP - that I could use some help with. The Inbox indicates that there are 47 unread messges, and when I hightlight the Inbox in the navigation pane the Status Bar at the pottom of the Outlook window says that there are 47 total items in the Inbox, but when I look at the message list, there are only 19 messages visible. I've verified that the View settings are set to show Messages, and I've verified that the exact same situation exists in the Unread Mail folder. There are no ...

Offline Terminal DataBase & Custom Table
Hello, I use the terminal database offline withe my POS (rms 1.3R). I create my database with Pos administrator, specifie the server for offline database. When I generate Z report , the database synchronize items and customers. But my problem is : I have a custom Table with Price connected to the Item's table, and this table doesn't synchronize with the offline database. Is it a solution to my problem ? or a tips ? Thanks Sorry for my english. Math ...

line chart using letters not numbers
I am trying to graph reading levels for a class, the levels are letters not numbers. My data would include; student name, level, month, (example: Lily, U, September). I understand I need to make a line chart but how do I change the values to letters not numbers. Thank you Assign a code, which may be like A=5, B=4, C=3, etc. Plot this numerical data, then use an artificial axis to place the text labels along the axis at positions corresponding to their numerical values: http://peltiertech.com/Excel/Charts/ArbitraryAxis.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and C...

Prevent the table modification message in action query
-- Wendy "Wendy" <Wendy@discussions.microsoft.com> wrote nothing in message news:3702E87B-3F08-42A5-9A04-4B7592A46FE6@microsoft.com... See: Action queries: suppressing dialogs, while knowing result at: http://allenbrowne.com/ser-60.html -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. ...

Auto updating pivot tables using external XML data
Hi all, I'm new to Excel and have been playing with importing XML data and pivot tables recently. My goal is this: I have bunch of XML files in a directory. I'd like to generate pivot tables and "visualize" the data in them. Once my excel sheet looks good, I'd like to publish it as an HTML file so everyone else can view it using Internet Explorer. In fact, I already did all this. Here is the tough part though: These XML files get updated from time to time. And when I open the Internet Explorer, I want to see those latest data in XMLs. In my case, Excel publishes the ...

Pass thru
In a Pass thru query, I want to select the meter_number, acct_period, prod_period, dth from the dbo.con_vols table and the mcf and btu from the dbo.meas table where the meter_number, acct_period and prod_period are equal to the meter_number, acct_period and prod_period in the dbo.con_vols table. Can anyone help, please? I know I need an INNER JOIN, but am not sure how to do that. Thank you in advance! On Mon, 6 Aug 2007 09:48:01 -0700, Liz C <LizC@discussions.microsoft.com> wrote: >In a Pass thru query, I want to select the meter_number, acct_period, >prod_period, dth fro...

URGENT: IF Function in Pivot Table Calculated Field
I am having trouble with an IF function call in the formula of calculated field in a pivot table. The pivot table has a calculated field called 'SDLT Tapes Required containing the following formula: =IF('Backup Media Type'="SDLT",'Total Backup Capacit Requirement'/220,0) where 'Backup Media Type' and 'Total Backup Capacity' are fields in th pivot table. 'Backup Media Type' contains text values such as "SDLT and "DLT 20/40". The field always displays a result of 0 even though there are rows i which the 'Backup Medi...