VBA-Add Column Totals

[Chris Linger]

I have a series of reports I would like to know VBA code
to add totals to the columns as part of code that displays
a range in print preview mode. But then needs to
be "cleared" when closing, so that additional records
(rows of data) can be added via data form. Can anyone
assist? Thanks, Chris

 

[Tom Ogilvy]

set rng = ActiveSheet.UsedRange
set rng = rng.rows(rng.rows.count).offset(1,0).Cells
rng.FormulaR1C1 = "=Sum(R[-1]C:R1C)"
ActiveSheet.Printout
rng.EntireRow.Delete

 

[Charles]

Chris,

I hope the following helps. It assumes you want to total colunn a.
You can make changes to suit your needs.

Sub add_column()
Application.ScreenUpdating = False
Dim irng As Range
Dim firstcell, lastcell
Set irng = Worksheets("sheet2").Cells(1, 1).CurrentRegion
lastrow = Range("a1").End(xlDown).Select
firstcell = "A1"
lastcell = ActiveCell.Row
TheFormula = "=sum(" & firstcell & ":" & "A" & lastcell & ")"
ActiveCell.Offset(1, 0).Value = TheFormula
ActiveWindow.SelectedSheets.PrintPreview
Selection.Offset(1, 0).ClearContents
End Sub

Charle