subtotaling worksheets

G

GazMo

I have 2 workbookbooks each with numerous worksheets of varying length
is there a way to automatically put subtotals under the last row i
each (preferably only under the Jan-Dec Columns only)
 
V

vehl

You could write a macro to do this, but it might be a little complex.
Are you familiar with VBA
 
D

Dave Peterson

Are Jan-Dec always in the same columns?

I'm gonna assume yes and that their in columns C:N.

Option Explicit
Sub testme01()

Dim Wks As Worksheet
Dim LastRow As Long

For Each Wks In ActiveWorkbook.Worksheets
With Wks
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Cells(LastRow + 1, "C").Resize(1, 12).FormulaR1C1 _
= "=subtotal(9,R2C:R[-1]C)"
End With
Next Wks

End Sub


I used =subtotal(9,...) which sums the range.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
D

Dave Peterson

Another suggestion.

Put your subtotals in Row 1, headers in row 2, and data in rows 3:65536.

Then you can put enter your formula without concern:

=subtotal(9,C3:c65536)

If you apply data|filter to your headers and data, this formula will show you
the results of the filtered data--and you won't have to scroll down to see the
totals--especially if you window|freeze panes so that the headers/subtotals are
always visible.
 
Top