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

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
"hsg" wrote:

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

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,

"tina" wrote:

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
"hsg" wrote:

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