Adding Totals to My Existing VBA Code

[Justinian]

I have a spread that looks like this:
<b>@TASK185_-03</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:105px;" /><col style="width:69px;" /><col style="width:47px;" /><col style="width:138px;" /><col style="width:68px;" /><col style="width:47px;" /><col style="width:68px;" /><col style="width:60px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-size:8pt; ">S16000804943</td><td style="font-size:8pt; text-align:right; ">12/28/2009</td><td style="font-size:8pt; text-align:right; ">1901</td><td style="font-size:8pt; ">1/10 Trash Removal</td><td style="font-size:8pt; text-align:right; ">174.55</td><td style="font-size:8pt; text-align:right; ">0.00</td><td style="font-size:8pt; text-align:right; ">174.55</td><td style="font-size:8pt; text-align:right; ">1/21/2010</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="font-size:8pt; ">S16000804943</td><td style="font-size:8pt; text-align:right; ">12/28/2009</td><td style="font-size:8pt; text-align:right; ">1901</td><td style="font-size:8pt; ">1/10 Trash Removal</td><td style="font-size:8pt; text-align:right; ">117.83</td><td style="font-size:8pt; text-align:right; ">0.00</td><td style="font-size:8pt; text-align:right; ">117.83</td><td style="font-size:8pt; text-align:right; ">1/21/2010</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="font-size:8pt; ">S150419H</td><td style="font-size:8pt; text-align:right; ">12/31/2009</td><td style="font-size:8pt; text-align:right; ">1901</td><td style="font-size:8pt; ">Remove Debris</td><td style="font-size:8pt; text-align:right; ">36.19</td><td style="font-size:8pt; text-align:right; ">0.00</td><td style="font-size:8pt; text-align:right; ">36.19</td><td style="font-size:8pt; text-align:right; ">1/21/2010</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="font-size:8pt; "> </td><td style="font-size:8pt; "> </td><td style="font-size:8pt; "> </td><td style="font-size:8pt; "> </td><td style="font-size:8pt; "> </td><td style="font-size:8pt; "> </td><td style="font-size:8pt; "> </td><td style="font-size:8pt; "> </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="font-size:8pt; ">S150773H</td><td style="font-size:8pt; text-align:right; ">1/22/2010</td><td style="font-size:8pt; text-align:right; ">1901</td><td style="font-size:8pt; ">Remove Pallets</td><td style="font-size:8pt; text-align:right; ">18.36</td><td style="font-size:8pt; text-align:right; ">0.00</td><td style="font-size:8pt; text-align:right; ">18.36</td><td style="font-size:8pt; text-align:right; ">2/11/2010</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="font-size:8pt; ">S16000820567</td><td style="font-size:8pt; text-align:right; ">1/28/2010</td><td style="font-size:8pt; text-align:right; ">1901</td><td style="font-size:8pt; ">2/10 Trash removal</td><td style="font-size:8pt; text-align:right; ">206.97</td><td style="font-size:8pt; text-align:right; ">0.00</td><td style="font-size:8pt; text-align:right; ">206.97</td><td style="font-size:8pt; text-align:right; ">2/18/2010</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="font-size:8pt; "> </td><td style="font-size:8pt; "> </td><td style="font-size:8pt; "> </td><td style="font-size:8pt; "> </td><td style="font-size:8pt; "> </td><td style="font-size:8pt; "> </td><td style="font-size:8pt; "> </td><td style="font-size:8pt; "> </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="font-size:8pt; ">S16000837182</td><td style="font-size:8pt; text-align:right; ">2/28/2010</td><td style="font-size:8pt; text-align:right; ">1901</td><td style="font-size:8pt; ">3/10 Trash Removal</td><td style="font-size:8pt; text-align:right; ">117.83</td><td style="font-size:8pt; text-align:right; ">0.00</td><td style="font-size:8pt; text-align:right; ">117.83</td><td style="font-size:8pt; text-align:right; ">4/9/2010</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="font-size:8pt; ">S16000866474</td><td style="font-size:8pt; text-align:right; ">3/28/2010</td><td style="font-size:8pt; text-align:right; ">1901</td><td style="font-size:8pt; ">4/10 Trash Removal</td><td style="font-size:8pt; text-align:right; ">235.66</td><td style="font-size:8pt; text-align:right; ">0.00</td><td style="font-size:8pt; text-align:right; ">235.66</td><td style="font-size:8pt; text-align:right; ">4/29/2010</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="font-size:8pt; "> </td><td style="font-size:8pt; "> </td><td style="font-size:8pt; "> </td><td style="font-size:8pt; "> </td><td style="font-size:8pt; "> </td><td style="font-size:8pt; "> </td><td style="font-size:8pt; "> </td><td style="font-size:8pt; "> </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="font-size:8pt; ">S16000881978</td><td style="font-size:8pt; text-align:right; ">4/28/2010</td><td style="font-size:8pt; text-align:right; ">1901</td><td style="font-size:8pt; ">5/10 Trash removal</td><td style="font-size:8pt; text-align:right; ">117.83</td><td style="font-size:8pt; text-align:right; ">0.00</td><td style="font-size:8pt; text-align:right; ">117.83</td><td style="font-size:8pt; text-align:right; ">5/27/2010</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="font-size:8pt; "> </td><td style="font-size:8pt; "> </td><td style="font-size:8pt; "> </td><td style="font-size:8pt; "> </td><td style="font-size:8pt; "> </td><td style="font-size:8pt; "> </td><td style="font-size:8pt; "> </td><td style="font-size:8pt; "> </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="font-size:8pt; ">S151495H</td><td style="font-size:8pt; text-align:right; ">5/24/2010</td><td style="font-size:8pt; text-align:right; ">1901</td><td style="font-size:8pt; ">Debris Removal</td><td style="font-size:8pt; text-align:right; ">37.21</td><td style="font-size:8pt; text-align:right; ">0.00</td><td style="font-size:8pt; text-align:right; ">37.21</td><td style="font-size:8pt; text-align:right; ">6/17/2010</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td style="font-size:8pt; ">S151593H</td><td style="font-size:8pt; text-align:right; ">5/25/2010</td><td style="font-size:8pt; text-align:right; ">1901</td><td style="font-size:8pt; ">Debris Removal</td><td style="font-size:8pt; text-align:right; ">30.31</td><td style="font-size:8pt; text-align:right; ">0.00</td><td style="font-size:8pt; text-align:right; ">30.31</td><td style="font-size:8pt; text-align:right; ">6/17/2010</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td style="font-size:8pt; ">S16000898099</td><td style="font-size:8pt; text-align:right; ">5/28/2010</td><td style="font-size:8pt; text-align:right; ">1901</td><td style="font-size:8pt; ">6/10 Trash removal</td><td style="font-size:8pt; text-align:right; ">117.83</td><td style="font-size:8pt; text-align:right; ">0.00</td><td style="font-size:8pt; text-align:right; ">117.83</td><td style="font-size:8pt; text-align:right; ">6/24/2010</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td style="font-size:8pt; "> </td><td style="font-size:8pt; "> </td><td style="font-size:8pt; "> </td><td style="font-size:8pt; "> </td><td style="font-size:8pt; "> </td><td style="font-size:8pt; "> </td><td style="font-size:8pt; "> </td><td style="font-size:8pt; "> </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td style="font-size:8pt; ">S16000927554</td><td style="font-size:8pt; text-align:right; ">6/28/2010</td><td style="font-size:8pt; text-align:right; ">1901</td><td style="font-size:8pt; ">7/10 Trash Removal</td><td style="font-size:8pt; text-align:right; ">117.83</td><td style="font-size:8pt; text-align:right; ">0.00</td><td style="font-size:8pt; text-align:right; ">117.83</td><td style="font-size:8pt; text-align:right; ">7/22/2010</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >18</td><td style="font-size:8pt; "> </td><td style="font-size:8pt; "> </td><td style="font-size:8pt; "> </td><td style="font-size:8pt; "> </td><td style="font-size:8pt; "> </td><td style="font-size:8pt; "> </td><td style="font-size:8pt; "> </td><td style="font-size:8pt; "> </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >19</td><td style="font-size:8pt; ">S16000944006</td><td style="font-size:8pt; text-align:right; ">7/28/2010</td><td style="font-size:8pt; text-align:right; ">1901</td><td style="font-size:8pt; ">8/10 Trash removal</td><td style="font-size:8pt; text-align:right; ">117.83</td><td style="font-size:8pt; text-align:right; ">0.00</td><td style="font-size:8pt; text-align:right; ">117.83</td><td style="font-size:8pt; text-align:right; ">8/19/2010</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >20</td><td style="font-size:8pt; "> </td><td style="font-size:8pt; "> </td><td style="font-size:8pt; "> </td><td style="font-size:8pt; "> </td><td style="font-size:8pt; "> </td><td style="font-size:8pt; "> </td><td style="font-size:8pt; "> </td><td style="font-size:8pt; "> </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >21</td><td style="font-size:8pt; ">S16001023892</td><td style="font-size:8pt; text-align:right; ">11/28/2010</td><td style="font-size:8pt; text-align:right; ">1901</td><td style="font-size:8pt; ">12/10 Trash Removal</td><td style="font-size:8pt; text-align:right; ">570.45</td><td style="font-size:8pt; text-align:right; ">0.00</td><td style="font-size:8pt; text-align:right; ">570.45</td><td style="font-size:8pt; text-align:right; ">12/30/2010</td></tr></table> <br /><br /><span style="font-family:Arial; font-size:9pt; font-weight:bold;background-color:#ffffff; color:#000000; ">Excel tables to the web >> </span><a style ="font-family:Arial; font-size:9pt; color:#fcf507; background-color:#800040; font-weight:bold;" href="http://www.excel-jeanie-html.de/index.php?f=1

I used the following code to get it to look this way:
Public Sub SplitDates()
Dim i As Long, _
LR As Long

With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With
LR = Range("H" & Rows.Count).End(xlUp).Row
For i = LR To 3 Step -1
If Month(Range("H" & i - 1).Value) <> Month(Range("H" & i).Value) Then
Rows(i).Insert shift:=xlDown
End If
Next i
With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
End With
End Sub


I want to know how to add code so I can quickly total each month. ANy ideas?

 

[Justinian]

Can anyone help? MVPs??

 

[VoG]

Try this with a copy of that sheet

Sub sbttls()
Dim aArea As Range
For Each aArea In ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants).Areas
    Cells(aArea.Row + aArea.Rows.Count, 5).Value = WorksheetFunction.Sum(Range(Cells(aArea.Row, 5), Cells(aArea.Row + aArea.Rows.Count - 1, 5)))
    Cells(aArea.Row + aArea.Rows.Count, 6).Value = WorksheetFunction.Sum(Range(Cells(aArea.Row, 6), Cells(aArea.Row + aArea.Rows.Count - 1, 6)))
    Cells(aArea.Row + aArea.Rows.Count, 7).Value = WorksheetFunction.Sum(Range(Cells(aArea.Row, 7), Cells(aArea.Row + aArea.Rows.Count - 1, 7)))
Next aArea
End Sub

 

[Justinian]

Thank you! One last question. How can I alter the code so that the totals are bold?

 

[VoG]

Try like this

    Cells(aArea.Row + aArea.Rows.Count, 5).Value = WorksheetFunction.Sum(Range(Cells(aArea.Row, 5), Cells(aArea.Row + aArea.Rows.Count - 1, 5)))
    Cells(aArea.Row + aArea.Rows.Count, 5).Font.Bold = True

 

[Justinian]

Thank you very much!