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 = CDate(Range("trade_Date").Value)

vdate = Range("trade_Date").Value
vdate = Format(vdate, "dd-mmm-yy")
 '   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(Selection, Selection.End(xlDown)).Select
    Sheets("Email London2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
Who is Participating?
NorieVBA 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

Open in new window

Roy CoxGroup Finance ManagerCommented:
You could use an Excel Table with Total row
JCutcliffeAuthor Commented:
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.

All Courses

From novice to tech pro — start learning today.