Adding Sum Formula to Footer

  • Thread starter Thread starter Adam Clark
  • Start date Start date
A

Adam Clark

Hi Experts

I desperately need to add a sum formula to my footer, does
anyone know how to do this?

I need to sum colum D for a running total on each page, I
cant use a formula at the bottom of the page as I paste
values into the worksheet through a macro so this would
overwrite the formula.

Thanks so much
 
Hi Adam,

Why not put the total in row 1, then it doesn't get over-written?

other than that, you could get it with VBA code like so

Dim iLastRow as Long
Dim sh as Worksheet

For Each sh in Activeworkbook.Worksheets
iLastRow = sh.Cells(Rows.Count,"D").End(xlUp).Row
sh.Cells(iLastRow+1,"D").Formula = "=SUM(D1:D" & iLastRow
Next sh

but you will need to re-rn every time you add data.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Unless you wanted to print a single page at a time allowing you to calculate
the subtotal and define the results in the footer string, then this is not
supported. If you put the data in with a macro, there is no reason you
could not have the macro go in and insert rows, placing the subtotal formula
at the bottom of each page.
 
Adam,

Forgot the closing bracket

Dim iLastRow As Long
Dim sh As Worksheet

For Each sh In ActiveWorkbook.Worksheets
iLastRow = sh.Cells(Rows.Count, "D").End(xlUp).Row
sh.Cells(iLastRow + 1, "D").Formula = "=SUM(D1:D" & iLastRow & ")"
Next sh

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Just for clarification.
My answer assumes you have a single worksheet with multiple pages being
printed. Bob's answer appears to assume a page is equivalent to a
worksheet, thus multiple worksheets and his code puts a sum formul for
column D at the bottom of column D.
 
Adam,

If, as Tom suggests, you are looking for a total at the foot of each printed
page, as long as you don't mind the whole total for each page, rather than
the total to date on that page, then you could use this workbook event code

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim iLastRow As Long
Dim sh As Worksheet

With ActiveSheet
iLastRow = .Cells(Rows.Count, "D").End(xlUp).Row
.PageSetup.LeftFooter = "Total = " &
WorksheetFunction.Sum(Range("D1:D" & iLastRow))
End With

End Sub


If you want page to date totals, that is harder.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Back
Top