Give this a whirl... It uses your same general logic but is a little cleaner.

Sub test()

Rows(1).Insert
Range("B1").Value = "'Name"
Range("C1").Value = "'Roll No"
Range("D1").Value = "'Amount"
Range("E1").Value = "'Total"

Range("E2").FormulaR1C1 = "=SUM(RC[-1]:R[2]C[-1])"
Range("E2:E4").Copy Destination:= _
Range(Range("E2"), Cells(Rows.Count, "D").End(xlUp).Offset(0, 1))
Columns("E").Value = Columns("E").Value
With Columns("B:E")
.EntireColumn.AutoFit
.AutoFilter Field:=4, Criteria1:="<"
End With

End Sub
--
HTH...

Jim Thomlinson


"nospaminlich" wrote:

Hi

I have data in Cols B, C and D. The number of rows of data varies.

I'm trying to write a macro which puts a formula in E2 which sums D2:D4 then
selects cells E2:E4 (E2 has the formula and E3:E4 are blank) and copies them
down to the last row of data in Col D. This is the equivalent of typing the
formula in E2, highlighting E2:E4 then double-clicking in the bottom right
corner of E4 to autofill down.

I've got this far but it doesn't work as it puts the formula in every cell.

Please can I have some help

Thanks a lot

Kewa

----------------------------------

Rows("1:1").Select
Selection.Insert Shift:=xlDown
Range("B1").Select
ActiveCell.FormulaR1C1 = "'Name"
Range("C1").Select
ActiveCell.FormulaR1C1 = "'Roll No"
Range("D1").Select
ActiveCell.FormulaR1C1 = "'Amount"
Range("E1").Select
ActiveCell.FormulaR1C1 = "'Total"

Range("E2").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-1]:R[2]C[-1])"

Range("E2:e4").Select
Selection.Copy
Set frng = Range("E2:E4" & Range("D65536").End(xlUp).Row)
frng.FillDown

Columns(5).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("E1").Select
Application.CutCopyMode = False

Columns("B:E").Select
Columns("B:E").EntireColumn.AutoFit

Selection.AutoFilter
Selection.AutoFilter Field:=4, Criteria1:="<"
End Sub