Totaling a column on each printed page

[sobeitjedi]

Is there a way I can display the total of a column on each printed page?

I have a table which is about 6 columns long but is hundreds of rows down. I would like the total of one of these colums to be displayed on every printed page. Is this possible?

 

[VoG]

Assuming that you want the same grand total on each printed page...

Right click the Excel logo to the left of File on the menu bar, select View Code. Copy and paste in

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim Total
Total = Application.Sum(ActiveSheet.Range("A:A"))
ActiveSheet.PageSetup.CenterFooter = "Total of Column A = " & Total
End Sub

Change the references to column A to suit.

 

[sobeitjedi]

Unfortunately not ... I want the total for that page to appear on each printed page.

Any more ideas?

 

[VoG]

Try this - place the code in a regular module (ALT+F11, Insert Module).

Sub prttot()
Dim nPages As Integer, Lastrow As Long, RowsPerPage As Integer
Dim i As Long, StartRow As Long, EndRow As Long, Total
nPages = ExecuteExcel4Macro("GET.DOCUMENT(50)")
Lastrow = Cells(Rows.Count, 1).End(xlUp).Row
RowsPerPage = ExecuteExcel4Macro("GET.DOCUMENT(64)") - 1
For i = 1 To nPages
    StartRow = 1 + RowsPerPage * (i - 1)
    EndRow = StartRow + RowsPerPage - 1
    If EndRow > Lastrow Then EndRow = Lastrow
    Total = Application.Sum(ActiveSheet.Range("A" & StartRow & ":A" & EndRow))
    Range("G" & EndRow).Value = "Total A = " & Total
Next i
ActiveSheet.PrintOut
For i = 1 To nPages
    StartRow = 1 + RowsPerPage * (i - 1)
    EndRow = StartRow + RowsPerPage - 1
    If EndRow > Lastrow Then EndRow = Lastrow
    Range("G" & EndRow).ClearContents
Next i
End Sub

Close the code window. Tools > Macro > Macros, click on prttot then click the run button. This should print your sheet with subtotals per page (for column A) in column G.

 

[sobeitjedi]

I haven't tested the above, but I have four colums I want totalling - each total to appear under each column on the printed page. Is this asking too much of excel? Surely not?

 

[VoG]

Is this asking too much of excel? Surely not?

As far as I am concerned - yes. Excel seems to have an inconsistent method of applying page breaks. If I could find that method I could insert blank rows then apply formulas. I have been singularly unsuccessful in doing this.

If you can make do with the totals appearing in columns to the right then my code can be modified relatively easily. Otherwise you need a guru.

 

[sobeitjedi]

OK - I'll just have to give up then. Thanks for trying.