add rows with copy format of previous row

[Guest]

My worksheet contains following data:

a b c d
list total cost of balance
funds item fund
-----------------------------------------------------
r1 item1 2500.00
r2 item2 3000.00
r3 item3 2000.00
r4 TOTAL 50000.00 7500.00 42500.00

As I add another item, selecting cell a3,
r4 should shift one row down, with c4 becoming
c5 and the formula should become sum(c1:c4)

format of row 3 (font, colour etc) should get copied to row4

Please help how to achieve this with macro
(either by writting macro or through macro recorder).

thanks

harsh

 

[Guest]

hi
Try following though I am sure there is better way you run macro while in
total cell
Sub macronewrow()
If ActiveCell.Value = "total" Then
ActiveCell.EntireRow.Select
Selection.Insert Shift:=xlDown
ActiveCell.Offset(0, 2).Name = "myend"
Range("c2").Name = "mystart"
ActiveCell.Offset(1, 2) = "=sum(mystart:myend)"
Else
MsgBox "you must be in "total" cell"
End If
End Sub

 

[Guest]

thanks tina, it works after changing total to TOTAL.
However it works even if the line ActiveCell.Offset(1, 2) =
"=sum(mystart:myend)"
just before Else is removed.

thanks anyway,