Sub totalssheet1()
' This is to calculate totals on sheet1 where there are 2 groups of rows of data in cols g to k
' both groups cab vary in the number of rows
' the code assumes the first group starts in row 3
'Brian Warnock September 2008
Dim tcell As Range ' totals for first group
Dim tcell2 As Range ' totals for second
Dim scell As Range ' start of second
Sheets("sheet1").Select
Set tcell = Range("g3").End(xlDown).Offset(1, 0) 'Totals cells
tcell.Value = Application.WorksheetFunction.Sum(Range("g3", Range("g3").End(xlDown)))
tcell.Offset(0, 1).Value = Application.WorksheetFunction.Sum(Range("h3", Range("h3").End(xlDown)))
'tcell(offset(0,2).Value = Application.WorksheetFunction.Sum(Range("i3", Range("i3").End(xlDown)))
'etc
'format
Range(tcell, tcell.Offset(0, 4)).Select
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlDouble
.Weight = xlThick
End With
With Selection
.NumberFormat = "$#,##0.00"
.Font.Bold = True
.HorizontalAlignment = xlCenter
End With
'then get next section
Set scell = Cells(tcell.Row, tcell.Column).End(xlDown)
Set tcell2 = Cells(scell.Row + 1, scell.Column).End(xlDown).Offset(1, 0)
tcell2.Value = Application.WorksheetFunction.Sum(Range(scell, tcell2.Offset(-1, 0)))
tcell2.Offset(0, 1).Value = Application.WorksheetFunction.Sum(Range(scell.Offset(0, 1), tcell2.Offset(-1, 1)))
'etc
Range(tcell2, tcell2.Offset(0, 4)).Select
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlDouble
.Weight = xlThick
End With
With Selection
.NumberFormat = "$#,##0.00"
.Font.Bold = True
.HorizontalAlignment = xlCenter
End With
End Sub