Hi
Hopefully this will get you started. It doesn't do the column width, or the borders - got to leave you something to add.
Sub InsertTotals()
headrow = Range("A3").End(xlDown).Row + 2
Cells(headrow, "D").Value = "Sub - Total"
Cells(headrow, "D").Font.Bold = True
Cells(headrow, "E").Formula = "=sum(E3:E" & headrow - 2 & ")"
Cells(headrow + 2, "E").Formula = "=E" & headrow & "/$E" & headrow
Cells(headrow + 2, "E").NumberFormat = "0.00%"
Cells(headrow, "E").Resize(3, 1).Copy Destination:=Cells(headrow, "E").Resize(3, 7)
toprow = headrow + 4
headrow = Range("A" & toprow).End(xlDown).Row + 1
Cells(headrow, "D").Value = "Sub - Total"
Cells(headrow, "D").Font.Bold = True
Cells(headrow, "E").Formula = "=sum(E" & toprow & ":E" & headrow - 2 & ")"
Cells(headrow + 2, "E").Formula = "=E" & headrow & "/$E" & headrow
Cells(headrow + 2, "E").NumberFormat = "0.00%"
Cells(headrow, "E").Resize(3, 1).Copy Destination:=Cells(headrow, "E").Resize(3, 7)
With Cells(headrow + 4, "D")
.Value = "TOTAL ARREARS"
.Font.Bold = True
.Offset(0, 1).Formula = "=SUMIF($D$1:$D$" & headrow + 3 & ",""Sub - Total"",E1:E" & headrow + 3 & ")"
.Offset(0, 1).Font.Bold = True
.Offset(0, 1).NumberFormat = "#,#.00"
.Offset(2, 0).Value = "TOTAL ARREARS AS A PERCENTAGE"
.Offset(2, 0).Font.Bold = True
.Offset(2, 1).Formula = "=E" & headrow + 4 & "/$E" & headrow + 4
.Offset(2, 1).Font.Bold = True
.Offset(2, 1).NumberFormat = "0.00%"
.Offset(0, 1).Resize(3, 1).Copy Destination:=.Offset(0, 1).Resize(3, 7)
End With
End Sub
rylo
Bookmarks