setting page breaks

[Koisti]

I have setup some code to sort out information on orders to be printed out. Sometimes these orders only take up two lines and other times they may take up to 5 or 6 lines. The first column always has the order number, so if there ends up being four lines for the order, the first cell on each of those lines is the order number. Sometimes the page break ends up putting half of the order on one page and half on the next page. I want to set it up so that it will find where one order stops and the next one starts and set the page break between them. Is there a way to set a variable page break?

 

[xlbo]

Hi Koisti - I use this for just such circumstances:
'Inserts page breaks automatically at change in Range Value
Sub InsertPageBreaks()
a = 1
'i=2 means header gets ignored - if no header, use i=1
For i = 2 To ActiveSheet.Range("a65536&quot.End(xlUp).Row
tr = Range("B" & i).Text
nr = Range("B" & i + 1).Text
If nr <> tr Then
Set ActiveSheet.HPageBreaks(a).Location = Range(&quot;A&quot; & i + 1)
a = a + 1
Else
End If

Next i
End Sub

HTH Rgds
~Geoff~