Sub-Totals on Every Page

[James Duncan]

Hi there,

I'm trying to develop an Invoice in Excel that displays
page sub-totals on every page. However, every Invoice
will have a different number of pages and therefore I
need an automatic way of inserting the sub-total
formula's at the bottom of the page whenever a new page
is created. I would also like to insert a Balance Carried
Forward formula at the top of every page, except for the
first.

Is this even possible in Excel or do I need to export the
data to a report writer like Crystal Reports, which can
setup top and bottom frames for reoccurring data, etc?

Many thanks

James

 

[Vasant Nanavati]

This can be done but is not trivial. I slapped together a macro which will
put in the footer on each page the subtotal through that page. Try it out
and post back if something like this would work for you:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Cancel = True
Dim hpb As HPageBreak, i As Long
Application.EnableEvents = False
With ActiveSheet
For i = 1 To .HPageBreaks.Count + 1
.PageSetup.RightFooter = WorksheetFunction.Sum _
(Range(Range("F1"), Range("F" & .HPageBreaks _
(i).Location.Row)))
On Error GoTo ErrorHandler
.PrintOut i, i
Next
End With
ErrorHandler:
Application.EnableEvents = True
End Sub

The code goes in the ThisWorkbook module.

I'm sure there are better ways. I'm not sure this is foolproof either, but
it's a start.

 

[James Duncan]

Thanks for that. It doesn't work if you only have 1 page
but I'm sure that's easily fixed. I also found a few
other problems. For example, when i tried it with 2 pages
with 1 on the first page and 2 on the section page it
only printed 1 page but with the total calculated as 3.
This is the grand total rather than a page sub-total. All
of this is fixable I'm sure by playing with the code but
obviously you are seriously limited by the fact you can't
format the sub-total and arrange it at the correct place
on the page as you have to use the footer.

I guess there is no reason why you couldn't fix the
number of rows per page and then using the
HPageBreaks.Count to determine the number of pages move
to the relevant cell on each page and paste in the sub-
total formula.

Taking this one step further i guess you could monitor
the HPageBreaks.Count and whenever it changes paste in a
complete new invoice page with the formulas already
setup, etc. That should work shouldn't it... ???

Time to have a play I guess.

Thanks for the idea and help

James

 

[James Duncan]

haha I've done it I can now detect number of pages and
when they change. This will allow me to paste in the
relevant formulas for the sub-totals on each page, etc.

The code is very simple:

Dim iTotalPages As Integer

Private Sub Workbook_Open()
iTotalPages = ActiveSheet.HPageBreaks.Count + 1
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object,
ByVal Target As Range)
If ActiveSheet.HPageBreaks.Count + 1 <> iTotalPages
Then
iTotalPages = ActiveSheet.HPageBreaks.Count + 1
MsgBox "Number of pages has changed to " &
iTotalPages, vbInformation, "JD Coding Example"
End If
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As
Object, ByVal Target As Range)

End Sub


I'm going to move the main stub of code from SheetChange
to SheetSelectionChange for my purposes.

All I need to figure out now is how to disable the Insert
Page Break, as this messes up my code! Any ideas?

Thanks

James