Table of contents with page numbers in Excel
Solved
Chester Copperpot
Posted messages
12
Registration date
Status
Member
Last intervention
-
Chester Copperpot Posted messages 12 Registration date Status Member Last intervention -
Chester Copperpot Posted messages 12 Registration date Status Member Last intervention -
Hello everyone,
I would like to switch from Word to Excel for writing my reports.
My reports are mainly composed of Excel tables, and copying and pasting takes up a lot of my time, plus I don't really need the functions offered by Word.
Basically, I would like to use Excel sheets as chapters.
However, I'm struggling with the automatic table of contents with page numbers:
I have inserted the page numbers in the footer correctly.
When printing the entire workbook, the page numbers sequence correctly (if Sheet 1 has three pages, then Sheet 2 starts on page number 4).
However, when in "Page Layout" view, all sheets start with page number 1.
On the first sheet ("Table of Contents"), I can successfully list the names of all the sheets via a macro.
My question is the following:
I would like to display next to the name of each sheet the page number that we find when printing the entire workbook. I do not know how to extract this variable...
Thank you very much for your help!
Vincent
Configuration: Windows / Firefox 96.0
I would like to switch from Word to Excel for writing my reports.
My reports are mainly composed of Excel tables, and copying and pasting takes up a lot of my time, plus I don't really need the functions offered by Word.
Basically, I would like to use Excel sheets as chapters.
However, I'm struggling with the automatic table of contents with page numbers:
I have inserted the page numbers in the footer correctly.
When printing the entire workbook, the page numbers sequence correctly (if Sheet 1 has three pages, then Sheet 2 starts on page number 4).
However, when in "Page Layout" view, all sheets start with page number 1.
On the first sheet ("Table of Contents"), I can successfully list the names of all the sheets via a macro.
My question is the following:
I would like to display next to the name of each sheet the page number that we find when printing the entire workbook. I do not know how to extract this variable...
Thank you very much for your help!
Vincent
Configuration: Windows / Firefox 96.0
1 answer
-
Good evening
In VBA, the instruction to get the number of pages of a sheet is:
nbf = ExecuteExcel4Macro("GET.DOCUMENT(50)")
Example of a macro to collect the names of all sheets and the number of their first page during a total printSub sommaire() 'if the first Excel sheet starts at 1 set deb = 0 otherwise if, for example, sommaire is first on a numbered page page 1 set as here deb = 1 deb = 1 ' as the summary is in the first tab, loop through the other sheets For n = 2 To Sheets.Count Sheets(n).Select ' get the name of the sheet Sheets("Sommaire").Range("B" & n) = Sheets(n).Name ' write the nº of the 1st page Sheets("Sommaire").Range("C" & n) = deb + 1 'increment the variable with the number of pages of the sheet deb = deb + ExecuteExcel4Macro("GET.DOCUMENT(50)") Next Sheets("Sommaire").Select End Sub
Kind regards
Via
