How to input a Sum Formula in vraiable range in Excel

Hi Guys, I have this VB code in Excel which does a filter on a range looks for yesterdays date on row 20 in a tab called "Murex Accrual PnL" then pastes it to cell K12 in a tab called "Email London 2".The range it pastes is 9 columns. The amount of rows is variable depending on the day. In cell R13 is a numeric column which I wish to insert a sum Formula at the bottom (eg. in Column K ) via code. The cell range would be depending on how many lines. Thus it would be from ("R13")to  ("R13").End(xlDown) and the sum formula would go in ("R13"). End(xlDown).  Offset(1).
Sub NewDeals()
Dim vdate As String

vdate = Format(vdate, "dd-mmm-yy")
'
Sheets("Murex_Accrual_PnL").Select
'   Rows("20:20").Select
'  Selection.AutoFilter
ActiveSheet.Range("\$A\$20:\$AZ\$250000").AutoFilter Field:=14, Criteria1:=vdate
'ActiveWindow.SmallScroll ToRight:=33
ActiveSheet.Range("\$A\$20:\$AZ\$250000").AutoFilter Field:=42, Criteria1:= _
">=500", Operator:=xlOr, Criteria2:="<=-500"

Range("I20:Q20").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Email London2").Select
Range("K12").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Who is Participating?

VBA ExpertCommented:
Would the SUM only be for column K and only sum that column?

If so try this.
``````With Sheets("Email London2")
.Range("K12").PasteSpecial Paste:=xlPasteValues
.Range("K" & Rows.Count).End(xlUp).Offset(1).FormulaR1C1 = "=SUM(R12C11:R[-1]C)"
End With
``````
0

Group Finance ManagerCommented:
You could use an Excel Table with Total row
0

Author Commented:
SUPERB
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.