automated page numbers with VBA

nancyo

Active Member
Joined
Mar 25, 2002
Messages
255
I have many workbooks which contain many worksheets. Some of the worksheets have multiple pages.

Currently, I have to manually enter all the page numbers in the header (i.e. Page 1 of 87). The total number of pages includes all the worksheets (and all of their pages).

I have looked through VBA help and tried a number of combinations of the formatting codes for headers, and none of them differentiates the different worksheets. By this, each worksheet starts over as page 1.

Any suggestions? The major reason I want to be able to do this is I want to be able to add / subtract worksheets and NOT have to manually changes all the headers.
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Hi nancyo,

This can be done in VBA, but is a bit involved, mainly because the Excel object model does not provide properties relating to page numbering, such as the total number of pages that a worksheet will print, etc. Thus your code has to figure out the number of pages each worksheet will print, and add them up to get the total number of pages for all worksheets. To count the pages you have to use the number of pagebreaks. I believe that the number of pages in a worksheet is the (number of horizontal page breaks + 1) x (number of vertical page breaks + 1).

Once you have the total count, I believe your code will need to print each page, one at a time, putting the numbers in the headers individually. If you have a lot of other stuff in the header in addition to the page number such that finding where the page number should be written could be a problem, simply put a text string like

<pageno> of <totalpages>

in the header. Then have your VBA code search and replace the <pageno> string with the computed page number, and <totalpages> with the computed total page count.

I hope this at least gets you started.
 
Upvote 0
Try the following which should print all the sheets in the workbook with the required page numbers in A1.
For each sheet, therefore, you need to include row 1 in "Rows to repeat at top" before running the macro.


Sub PageCountHeader()
Dim ws As Worksheet, totPages%, p%, x%
For Each ws In Worksheets
totPages = totPages + Application.ExecuteExcel4Macro("GET.DOCUMENT(50,""" & ActiveSheet.Name & """)")
Next
p = 1
For Each ws In Worksheets
ws.Activate
For x = 1 To Application.ExecuteExcel4Macro("GET.DOCUMENT(50,""" & ActiveSheet.Name & """)")
[A1].Value = "Page " & p & "of " & totPages
ActiveSheet.PrintOut From:=x, To:=x
p = p + 1
Next x
[A1].ClearContents
Next ws
End Sub
 
Upvote 0
If you just put the standard "&[Page] of &[Pages]" in the header and select all the sheets at once, it should work fine. It does for me.
 
Upvote 0
On 2002-06-08 22:09, zzydhf wrote:
If you just put the standard "&[Page] of &[Pages]" in the header and select all the sheets at once, it should work fine. It does for me.


That is not what nancyo is looking for.

She wants the page numbers to be consecutive not by sheet but by workbook.

That is, if Sheet1 has 5 pages, then the first page of Sheet2 would be page 6.
 
Upvote 0
It does do that if you select all the sheets before printpreview or printing. Then if you just want the first sheet on page 2 (5 of 6) you would have to specify that in in the Print dialog box. I realize it is not automatic, but it works.

*edit*
Sub EditHeader()
Dim sh As Worksheet
For Each sh In Worksheets
sh.PageSetup.CenterHeader = "&P of &N"
Next

Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select
'you will have to put your sheetnames here
'or "sheets.select" for all sheets
ActiveWindow.SelectedSheets.PrintPreview
End Sub

_________________
Oh, you hate your job? Why didn't you say so? There's a support group
for that. It's called EVERYBODY, and they meet at the bar. - Drew Carey
This message was edited by zzydhf on 2002-06-09 10:10
This message was edited by zzydhf on 2002-06-09 10:16
 
Upvote 0
OK - I've read thru all your answers, and will give it a go this AM. Prefer a non-VBA approach, but will look at both. I will respond ASAP. THANKS!!!!!!!!!!!!!!!!!!!!!!!
 
Upvote 0
zzydhf - if i want to use the "[Page... option you suggested, how do I "select all the sheets at once"?
 
Upvote 0

Forum statistics

Threads
1,213,524
Messages
6,114,117
Members
448,549
Latest member
brianhfield

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top