Macro to determine row of page break on modified presentation sheet?

[pallaver]

I have a presentation sheet where the first five rows or so can be
changed, etc., to account for different information etc. - i.e. the
header of the sheet.

After the sheet header is data information contained in rows of all
the same height.

The problem is that depending one how these first five rows looks
(i.e. sometimes it's only 3 rows and thin, or sometimes all 5 and
fat), the page break occurs on different rows.

In my macro, when the user inputs information, if it goes past the
page break, I'd like a new sheet to be made, and the information to
start being displayed on that new sheet.

Right now I have a manual page break input question (i.e. macro asks
in the beginning please input the last row on the sheet before the
page break). Is there a way I can make the macro do this
automatically? I.E. detect where the page break is and identify the
row number of the last row before the page break?

Thanks, an odd question, I'll start with a dummy worksheet and macro
to try and figure this out....

-np

 

[FSt1]

hi
read up on the HPageBreak and VPageBreak properties in vb help.
also see this site.
http://www.ozgrid.com/forum/showthread.php?t=73446

example
Dim pb As Variant
Dim r As Range
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")
For Each pb In ws.HPageBreaks
Set r = ws.Range("A" & pb.Location.Row)
MsgBox r.Address
Next pb

regards
FSt1

 

[pallaver]

Thanks, perfect.
pb.Location.Row - 1 is exactly the variable I was looking for.