Adding a range in VBA

No need to select.

See if this points you in the right direction

Dim LastRow As Long
Dim StartRow As Long

StartRow = 3
LastRow = 10

With ActiveCell
    .Formula = "=SUM(A" & StartRow & ":A" & LastRow & ")"
    .AutoFill Destination:=Range(ActiveCell.Address, "A" & LastRow + 6), Type:=xlFillDefault
End With

VBA Noob