variable page breaks

J

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.
 
R

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---
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top