subtotaling worksheets

G

GazMo

Thanks Dave your code works great ... however, is there a way to forma
the subtotals to 2 decimal places and also format in Bold Red so tha
they stand out
 
V

vehl

Thanks for your code, Dave. I didn't know you could do it that way.
That's much more efficient than what I've been doing.

GazMo, one way to format the subtotals, assuming you took Dave'
suggestion and moved them to the first row:

Option Explicit
Sub temp()
Dim Wks As Worksheet

For Each Wks In ActiveWorkbook.Worksheets
Wks.Activate
Rows("1:1").Select
With Selection
.Font.Bold = True
.Interior.ColorIndex = 3
.Interior.Pattern = xlSolid
.NumberFormat = "0.00"
End With
Next Wks
End Sub

or use .NumberFormat = "$#,##0.00" for currency
 
D

Dave Peterson

If you kept them at the bottom:


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
With .Cells(LastRow + 1, "C").Resize(1, 12)
.FormulaR1C1 = "=subtotal(9,R2C:R[-1]C)"
.NumberFormat = "0.00"
.Font.Bold = True
.Font.ColorIndex = 3
End With
End With
Next Wks

End Sub


..colorindex = 3 is red in my workbook. (but can be changed by the user).
 
Top