Inserting rows after Subtotal

[MattB]

Is there a way without using code to insert 4 rows after each subtotal?
If code is the way forward could somebody give me a shove in the right direction please.
Cheers.

[Iain Lewis]

This code will do it. As I don't know where on the sheet the Subtotal is you will need to select a cell in the row beneath the Subtotal.

Sub aaa()
Range(ActiveCell, ActiveCell.Offset(3, _ 0)).Select
Selection.EntireRow.Insert
End Sub

If you'd like it explained then just give me a shout.

[MattB]

Cheers Ian
The sheet is made up of hundreds of subtotals and i'd like to insert 4 rows after each of them; the first cell beneath the subtotal in this instance is A15, however i need to run similar reports daily with different info. So ideally i'd like a macro that can insert the rows after the subtotals for each report that i run.
Matt

[Iain Lewis]

Are you using the Subtotal wizard to apply the subtotal ?

[MattB]

Sorry for the delay, i just use Data>Subtotals.

[MattB]

Sorry to be a pain but i'm really struggling with this task!, any suggestions before I spend the next 3 hours inserting rows?!!

[Iain Lewis]

Here you go. This code looks for "x Total" in column A ( just change a65536 to which ever column you need ).

Sub aaa()
Range("a65536").End(xlUp).Offset(-2, 0).Select
Do Until ActiveCell.Row = 1
If Right(ActiveCell, 5) = "Total" Then
Range(ActiveCell.Offset(1, 0), ActiveCell.Offset(3, 0)).EntireRow.Insert
ActiveCell.Offset(-1, 0).Select
Else
ActiveCell.Offset(-1, 0).Select
End If
Loop
End Sub

[MattB]

Good man!
I'll give it a shot

[MattB]

Excellent!, how much do I owe you?!
I now need something slightly more advanced.
Each of the subtotals gives me a figure in column H, in the cell to the right of that I need to i need to multiply this total by 1.056, is it possible to incorporate this into the code so that it calculates it for every subtotal in the sheet.
What would be even better would be to have 2 blank cells to the right of the calculation (these have to be added manually) and a total in the next cell to the right which adds the 4 figures together.
Or am i pushing my luck!?

[MattB]

OK, i've managed that but I do need 1 more thing!
The data that i said needs to be added manually in the cell next to the 0.056 calculation may not need to be after all.
The data within the subtotal is a total of all of the in-house material for that particular part code. We apply a certain % to this, hence the 0.056 calculation.
However, there are also codes within the subtotal that are outsourced - I have highlighted these by conditional formatting (they return a code in the L column which differentiates them from the in-house material).
The outsourced codes have a different % applied, so the problems are:
Can a macro be written to enable the subtotal to subtract all of the costs that are subcontracted?, and
Can the subtotal cost be added into the necessary cell with a % applied to it?
This is probably clear as mud!