AMK4,
Does this get you closer:
Dim i As Long
Const ROWS_PER_PAGE As Long = 17 'Or whatever suits you

With ActiveSheet
.ResetAllPageBreaks
For i = ROWS_PER_PAGE To .UsedRange.Rows.Count Step ROWS_PER_PAGE
.HPageBreaks.Add .Cells(i, "A")
Next
End With

NickHK

"AMK4" wrote in message
...

Dick Kusleika Wrote:
AMK4:

I don't know if I completely understand your question. If you want
the
print area to adjust to the size of your data, you can define a
worksheet
level name. Insert Name Define - put Sheet3!Print_Area in the name
box.
In the refersto box, put

=Offset(A1,0,0,counta($a:$a),17)

Now the print area will be however many entries you have in column A
and 17
column wide (Q).

Not quite. Ok, let's see if I can explain it better:

Sheet 1 has a column with names in it. For the sake of this
experiment, let's say the names are all in column D.

Sheet 2 has a form designed on it that covers cells A1:Q18.

Now, I have a VBA macro that will:
- loop through all the names on Sheet 1, column D
- for each name, it copies the form from Sheet 2 and dumps it on
Sheet 3, and inserting the name on the form.
- Moves on to the next name on the list.

When it's done, if I simply print out Sheet 3, I will end up with page
breaks in the middle of some of the cells that contain stuff, like some
of the forms will be split onto two pages. I want to be able to have it
(programmatically) set how many forms per sheet of paper (page break) it
will print, instead of having the user have to go into View - Page
Break Preview and manually have to adjust the page breaks so that none
of the forms get cut off.


--
AMK4
------------------------------------------------------------------------
AMK4's Profile:
http://www.excelforum.com/member.php...o&userid=19143
View this thread: http://www.excelforum.com/showthread...hreadid=509182