Sub pageSubtotals()
Dim aws As Worksheet
Set aws = ActiveSheet
ActiveWindow.View = xlPageBreakPreview
i = 1
Set tprng = Range("C4")
Do
pbl = ActiveSheet.HPageBreaks.Item(i).Location.Address
Range(pbl).Select
Set hpb = ActiveSheet.HPageBreaks.Item(i)
Range(hpb.Location.Offset(-2, 0), hpb.Location.Offset(1, 0)).EntireRow.Insert
Range(pbl).Offset(-2, 0).EntireRow.Interior.Color = Range(pbl).Offset(-4, 0).Interior.Color
Range(pbl).Offset(-1, 0).EntireRow.Interior.Color = RGB(0, 255, 0)
Range(pbl).Offset(0, 0).EntireRow.Interior.Color = RGB(0, 255, 0)
Range(pbl).Offset(1, 0).EntireRow.Interior.Color = Range(pbl).Offset(-3, 0).Interior.Color
Range(pbl).EntireRow.PageBreak = xlPageBreakManual
Range(pbl).Offset(-1, 2).NumberFormat = Range(pbl).Offset(-3, 2).NumberFormat
Range(pbl).Offset(-1, 2).Formula = "=sum(" & tprng.Address & ":" & Range(pbl).Offset(-3, 2).Address & ")"
Range(pbl).Offset(0, 2).NumberFormat = Range(pbl).NumberFormat
Range(pbl).Offset(0, 2) = "=" & Range(pbl).Offset(-1, 2).Address
Range(pbl).Offset(-1, 3) = "Balance"
Range(pbl).Offset(0, 3) = "Carried forward"
Set tprng = Range(pbl)
i = i + 1
Loop While i <= ActiveSheet.HPageBreaks.Count
pbl = Range("C" & ActiveSheet.Rows.Count).End(xlUp).Offset(3, 0).Address
Range(pbl).Offset(-2, 0).EntireRow.Interior.Color = Range(pbl).Offset(-4, 0).Interior.Color
Range(pbl).Offset(-1, 0).EntireRow.Interior.Color = RGB(0, 255, 0)
Range(pbl).Offset(-1, 0).NumberFormat = Range(pbl).Offset(-3, 0).NumberFormat
Range(pbl).Offset(-1, 0).Formula = "=sum(" & tprng.Address & ":" & Range(pbl).Offset(-3, 2).Address & ")"
Range(pbl).Offset(-1, 3) = "Total"
ActiveWindow.View = xlNormalView
End Sub
Sub pageSubtotals()
Dim aws As Worksheet
Set aws = ActiveSheet
ActiveWindow.View = xlPageBreakPreview
i = 1
'This is a variable used for determining the starting of the sum range
Set tprng = Range("C4")
'For each of the pagebreaks. The "for each" method does not work here if the number
' of page breaks increases while inserting rows.
Do
pbl = ActiveSheet.HPageBreaks.Item(i).Location.Address
Range(pbl).Select
Set hpb = ActiveSheet.HPageBreaks.Item(i)
'this is the range starting from two rows above the horizontal pagebreak
'down to one row below the pagebreak (a total of 4 rows, one each for the
'balance and carry over and one each above and below them)
Range(hpb.Location.Offset(-2, 0), hpb.Location.Offset(1, 0)).EntireRow.Insert
'I bet you know what this is
Range(Range(pbl).Offset(-2, 0), Range(pbl).Offset(1, 0)).Offset(0, 4) = "##"
Range(pbl).Offset(-2, 0).EntireRow.Interior.Color = Range(pbl).Offset(-4, 0).Interior.Color
Range(pbl).Offset(-1, 0).EntireRow.Interior.Color = RGB(0, 255, 0)
Range(pbl).Offset(0, 0).EntireRow.Interior.Color = RGB(0, 255, 0)
Range(pbl).Offset(1, 0).EntireRow.Interior.Color = Range(pbl).Offset(-3, 0).Interior.Color
Range(pbl).EntireRow.PageBreak = xlPageBreakManual
Range(pbl).Offset(-1, 2).NumberFormat = Range(pbl).Offset(-3, 2).NumberFormat
Range(pbl).Offset(-1, 2).Formula = "=sum(" & tprng.Address & ":" & Range(pbl).Offset(-3, 2).Address & ")"
Range(pbl).Offset(0, 2).NumberFormat = Range(pbl).NumberFormat
Range(pbl).Offset(0, 2) = "=" & Range(pbl).Offset(-1, 2).Address
Range(pbl).Offset(-1, 3) = "Balance"
Range(pbl).Offset(0, 3) = "Carried forward"
Set tprng = Range(pbl).Offset(0, 2)
i = i + 1
Loop While i <= ActiveSheet.HPageBreaks.Count
pbl = Range("C" & ActiveSheet.Rows.Count).End(xlUp).Offset(3, 0).Address
Range(pbl).Offset(-2, 0).EntireRow.Interior.Color = Range(pbl).Offset(-4, 0).Interior.Color
Range(pbl).Offset(-1, 0).EntireRow.Interior.Color = RGB(0, 255, 0)
Range(pbl).Offset(-1, 0).NumberFormat = Range(pbl).Offset(-3, 0).NumberFormat
Range(pbl).Offset(-1, 0).Formula = "=sum(" & tprng.Address & ":" & Range(pbl).Offset(-3, 2).Address & ")"
Range(pbl).Offset(-1, -1) = "Total"
Range(Range(pbl).Offset(-2, 0), Range(pbl).Offset(-1, 0)).Offset(0, 2) = "##"
ActiveWindow.View = xlNormalView
End Sub
The only other way that I can think of would be to set a fixed number of rows per page and hardcode the page breaks rather than automatically set them. Then have a running total column with conditional formatting based on the row number.
Cheers
Rob H