You can set the page break by VBA above a specified row.
What you need to do therefore, is:
find how many rows you have,
divide by 5 and set this value to a variable.
set the page break,
do a test to see if there are more rows left and repeat
until the number of rows =0.
this sets the page break
Worksheets("Sheet1").Rows(25).PageBreak = xlPageBreakManual


A code like this should do the job:

Sheets("sheet1").Range("A65536").Select
Selection.End(xlUp).Select
row1 = ActiveCell.Row

numrows = row1 - 1 'sets number of rows of data to select
from

b = 0
a = Round(numrows / 5, 0)
For n = 1 To a
b = b + 5

Worksheets("Sheet1").Rows(b).PageBreak = xlPageBreakManual
Next

change 5 to whatever value you want to have as the number
of rows on a page.

this code removes the page break
Worksheets("Sheet1").Rows(25).PageBreak = xlPageBreakNone
again replacing 25 with the row number where the page
break occurs. Best this is done before updating the
workbook to ensure that you find the page breaks.

-----Original Message-----
Nope. Actually, I never used that feature! Works very
nicely! Is it true
that, if you're smart enough, you can make EXCEL clean
windows!

Only problem is that, currently, based on the total
number of rows, the
number of pages is computed by a macro and I would need
this resetting of
page breaks to be controlled by macro, too.

Ideally, I would like it to break the page only after
(three plus) a
multiple of 5 rows. Since it seems to want to put more
rows on page 1 than
page 2 (and, if there are 3 pages, more on two than
three, I'd guess), I
think I would want it to round down to the nearest
multiple of five. If it
turns out to need 1, 2, 3, or 4 pages (max), is there a
way to have a macro
tell it always to round down from what it automatically
computes, and
(re-)place the page breaks there?

Can anyone give me the macro code for that?

Thanks so much,
Grace

"DavidC" wrote in
message
...
Have you tried setting a page break at a point on the
sheet which gives you an even split?
-----Original Message-----
I notice that, when I ask EXCEL to print an exhibit on
2
pages, it decides
what to put on page 1 and page 2 and that page 1 always
seems to have more
data on it than page 2. I have tried altering either
top
and bottom margins
or header and footer, to try to get it to put a bit
more
(but still never
even quite half) on page 2. But it stubbornly won't
change anything. All
it does is shrink the size of the image on both pages,
still leaving more
than half the printed range on page 1. Both have the
same header (and I
suppose footer), by the way

I'm sure EXCEL has some method to its madness but is
there some way to get
EXCEL to spread the data more evenly across these two
pages? It's almost
as if there is something at the bottom of a "final"
page
that it thinks it
must leave extra room for? Please don't suggest a
solution that will print
out, say, a blank 3rd page.

Thanks
Grace


.



.