Trying to number print pages in VBA

Hi all. I am trying to print a workbook with several worksheets, some of which have a variable number of page breaks. I need to correctly number each page in the print, and this must happen dynamically via VBA since page sizes vary.

The number of page breaks per sheet is easy enough to figure out using code, eg

s6 = Sheets("commitmentsO").HPageBreaks.Count

So I can get the starting page number for each sheet by adding up the HPageBreaks for each prior sheet, eg

COpagestart = Notespageend + 1
COpageend = COpagestart + s6

Now I have code to add the page number in the footer, eg

With Sheets("commitmentsO").PageSetup
.Orientation = xlLandscape
.Order = xlOverThenDown
.FirstPageNumber = COpagestart
.CenterFooter = "Page " & COpagestart
End With

Then I finally print out all the visible sheets in the workbook:

ThisWorkbook.PrintOut

(I have edited the code just to show one sheet)

The problem is that this sheet ("commitmentsO") has 2 page breaks (ie 3 pages), but each page prints out with the same page number, in this case, 6.

My question is, how do I get correct page numbers on each of these 3 pages (6,7 & 8) ?

Ideas anyone?

Thanks
rog1111

Hi rog,

Is there a reason you are doing this through VBA rather than insert page numbers in the footer or header of the pages as this would surely be easier?

Hi TinTin

Several reasons, the number of sheets varies, the size of the sheets and number of pagebreaks vary, and the page numbering only starts on the 3 sheet of the workbook. I also have an index page which must be numbered correctly referring to the page number(s) of each sheet, it's all quite complex

rog

Sounds like this is being made more complicated than necessary although hard to tell without the full project.

Anyway, the reason you're getting the same page number all the time is because you're actually telling it to print the page number .CenterFooter = "Page " & COpagestartIf you change this line to .CenterFooter = "Page " & "&P"you should get the correct page numbers coming through.

In Excel, special codes are prefixed with an ampersand so the &[Page] code is &P and the &[Pages] code is &N. There are many more of these codes that can come in useful when creating headers/footers dynamically. You should be able to find a list on the net somewhere although I have a short list here should you need it.

TinTin,

You are partly right, it's easier just to add the page number to the footer tham muck around pagesetup in VBA. I thought that I'd tried this without success on the client box in Excel2002, it seems to work at home in 07, but I'll check on 02 again when Im back in the office tomorrow.

However, it seems that I still need to specify the start page (number) for each sheet, as I need to hide some sheets programmatically, depending on user options, but if I do nothing then page numbers come out incorrectly.

Regards,
rog1111