Grab what the printed page number will be to include in TOC

[jdash]

Hello,

If I have row references in a worksheet that I want to find out what the printed page number will be, how can I automatically pull the page number that the reference is on to be included in new cell (on the table of contents page)?

Please please anyone!

 

[ukmikeb]

Hi

For the # of Pages in the document -

NoofPgs = ActiveSheet.HPageBreaks.Count + 1

For the row number of the Page Break -

PgBreakRow =  ActiveSheet.HPageBreaks(PgCt).Location.Row - 1

where PgCt is the Page Number you're looking for and
PgBreakRow is the last row of that Page.

hth

 

[jdash]

Thanks Mike but I'm sort of still at a loss- is something this what I am supposed to do?

Sub PgCt()
    PgBreakRow = ActiveSheet.HPageBreaks(PgCt).Location.Row - 1
End Sub

Sub PgBreakRow()
    NoofPgs = ActiveSheet.HPageBreaks.Count + 1
End Sub

I'm really having trouble and have been trying to research a solution for 2 days now..

What seems most silly is that in Page Break preview Excel watermarks the page numbers right there. I'd think that there would be an easy way to just go ahead and insert those page numbers as a value in a cell to say what page data is on. Is the idea that you aren't supposed to print or format a book in Excel? It's crazy that I have to manually enter in each page number to a table of contents.. especially if those page numbers are likely to change.

I would love to see =PageCt($B1) as a formula or UDF...

Can you help me with that?

 

[shg]

See http://www.mcgimpsey.com/excel/udfs/pagenumber.html

 

[jdash]

thanks shg,
i was playing with that already and is very slow. I commented out the Application.volatile line, and still when I go to copy down it hangs way long.. I'm dealing with 13,000+ rows and each row is taking several seconds to compute. It would take an hour each time it needed to recalculate.

There must be a faster way to calculate, no?

 

[shg]

Dunno. If you're using Excel 2010, you could try setting application.printcommunication = false, but recognize that the pagination can only come from chatting with the printer driver, and those are slow conversations.

The code could probably be modified to create a collection of the page breaks in a first pass, then compute page numbers for any given cell from those without further discussion with the printer.

 

[jdash]

I'm on 2007. But to your point I had found this article: http://blogs.office.com/b/microsoft...add-a-table-of-contents-to-your-workbook.aspx

He seems to achieve it there by opening up the Print Preview box first, but I just can't break the code down enough that I can make it work for me. I am not a programmer (but I wish I was).