Insert Row w/ % Total, under Subtotal

[whizkid27]

Hi,

I am looking for a way, using VBA, to insert a row under each subtotal in my worksheet (an aging report). Column B in my spreadsheet contains total outstanding, while column c contains current outstanding invoices, while column d is invoices 30 - 60 days outstanding, and column e contains invoices over 60 days. This information gets subtotaled for domestic orders and international orders. I want to insert a row under each subtotal row so that column d will contain the subtotal in column d divided by the subtotal in column c (expressed as a %.) Each column will be divided by column c and shown as a percentage.

Is there a way to do this in VBA or otherwise?

Thanks for any help.

[Leith Ross]

Hello whizkid27,

Here is a macro you can use. Since you didn't say if there were a set number of rows that will be subtotaled, I am assuming this is not the case. However, the can accept the last row as variable. If it is not present then it will automatically count the rows until it finds either a blank line or non numeric value. Once the last entry is found, the new line is inserted and the values calculated.

_________________________________________________________________

Public Sub MyMacro1(Optional LastEntry_RowNumber As Long)

Dim ageCurrent As Single
Dim Upto60 As Single
Dim Over60 As Single

Dim NewLine As Long
Dim SubTotal As Long

If LastEntry_RowNumber = 0 Then
NewLine = Excel.WorksheetFunction.Count(Range("B:B")) + 1
Else
NewLine = LastEntry_RowNumber + 1
End If

With Rows(NewLine)
.Select
.Insert (xlShiftDown)
End With

SubTotal = NewLine - 1

ageCurrent = Cells(SubTotal, 3).Value
Upto60 = Cells(SubTotal, 4).Value / ageCurrent
Over60 = Cells(SubTotal, 5).Value / ageCurrent

Cells(NewLine, 4).Value = Format(Upto60, "##0.00%")
Cells(NewLine, 5).Value = Format(Over60, "##0.00%")

End Sub
_________________________________________________________________

Hope this helps,
Leith Ross

[whizkid27]

Thanks. I haven't had a chance to give a try yet, but I'll be sure to let you know.

THANK YOU!