variable page breaks

[joemeshuggah]

Is there a way to adjust page breaks based on variable criteria?

I have a macro that subtotals information by hierarchy (subtotals at the
bottom)...column b is what is being subtotaled (the hierarchy) and column c
is blank for the subtotal row (column c houses the employees that fall under
each hierarchy). For example

Column: B C
Header Row:Hierarchy Employee ID
Data Row: Accounting 123
Data Row: Accounting 456
Subtotal Row: Accounting Total 2
Data Row: Facilities 789
Subtotal Row: Facilities Total 1

The standard page break is fine, however, I do not want a particular
hierarchy to be split across a page...e.g. if a particular hierarchy is going
to spill over onto the next page, I would like the page break to be below the
previous hierarchy's subtotal. For example:

If the standard page break would occur like this:

Column: B C
Header Row: Hierarchy Employee ID
Data Row: Accounting 123
-------------------------------------------------
Data Row: Accounting 456
Subtotal Row: Accounting Total 2
Data Row: Facilities 789
Subtotal Row: Facilities Total 1

I would want the macro to recognize that the hierarchy is being split
between pages and adjust so that all of the information appears on the next
page.

 

[ryguy7272]

This will insert a pagebreak when there is a change in values:
Sub pagebreak()
Dim value1 As String
Dim value2 As String

Range("A2").Select
Do While ActiveCell.Value <> ""
value1 = ActiveCell.Value
ActiveCell.Offset(1, 0).Select
value2 = ActiveCell.Value
If value1 <> value2 Then
ActiveCell.EntireRow.Select
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell
Else
End If
Loop
End Sub

What you are talking about is quite easy to do in an Access report, with
grouping, but I'm not sure EXACTLY how to make Excel respond in the same way.
Maybe you would have to define the length of the page and then test for
values inside that range. Just a thought.

HTH,
Ryan---