VBA to AutoSUM selected Range

Question

I am working on automating some reports by pasting data from raw files into my Template called PA Reach. I have named a range "Sumrow" and I need VBA to autosum the numbers above in each of the loops after pasting the data in. Here is what I have and I'm getting errors:

Workbooks.Open datapath & datafile1 & OutputFileExt
  Sheets("Rep").Activate
  ActiveSheet.UsedRange.AutoFilter        

  For i = 1 To terrs_to_generate
    Workbooks(builder).Sheets("Control").Activate

    Range("Reportnum") = i
    currfile = Range("CurrFile").Value
    currterr = Range("CurrTerr").Value
    currRep = Range("currRep").Value
    terrname = Range("terrname").Value
    DMName = Range("DMName").Value
    TMStartDate = Range("TMstartdate").Value

    'open template
    Workbooks.Open templatepath & templatefile & OutputFileExt

    Set currtemplatefile = ActiveWorkbook

    Sheets("Control").Select
    Sheets("control").Range("terrname") = terrname
    Sheets("control").Range("repname") = currRep
    Sheets("control").Range("reportdate") = reportdate
    Sheets("control").Range("TMstartdate") = TMStartDate
    Sheets("control").Range("DMName") = DMName
    Sheets("control").Range("Territory") = currterr

    Workbooks(datafile1 & OutputFileExt).Activate
    Selection.AutoFilter field:=1, Criteria1:=currterr

    Range("A1").Select
    Range(Selection.Offset(1, 1).End(xlDown), Selection.End(xlToRight)).Copy
    currtemplatefile.Activate
    Sheets("PA Reach").Select
    Range("pasterange").Select
    Selection.PasteSpecial Paste:=xlPasteValues

    currtemplatefile.Activate
    Range("formatrow").Copy

    Range("pasterange").Select
    Range(Selection.End(xlToRight), Selection.End(xlDown).Offset(0, 0)).Select
    Selection.PasteSpecial Paste:=xlPasteFormats

    'Delete PasteRange
    Range("pasterange").Select
    Selection.EntireRow.Delete

    'Value Range Sheet so no formulas show
    ActiveSheet.UsedRange.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues

   Range("A1").Select



 Sheets("control").Delete
'need to add in sum total
Range("sumrow1").End(xlDown).Offset(1, 0) = "=Sum(" & Range("sumrow1").Address(True, True) & ")"



    'save as and close
     ActiveWorkbook.SaveAs Filename:=(reportpath & currfile & OutputFileExt)   'not working

You could clean that code up a lot following [Avoid Select/Activate](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) — Rdster, Feb 01 '17 at 14:56

Shai Rado · Answer 1 · 2017-02-01T15:06:08.093

0

There is no need to Select Range("sumrow"), and then use Selection, you can modify the Formula to the Range directly.

Try the code below, it will put the Sum formula at the row below the bottom row of your "sumrow" named range:

Range("sumrow").End(xlDown).Offset(1, 0) = "=Sum(" & Range("sumrow").Address(True, True) & ")"

edited Feb 01 '17 at 15:06

answered Feb 01 '17 at 14:55

Shai Rado

33,032
6
29
51

This is awesome, and it worked. However, it is SUMming sideways, how can I make it sum just the row above? – SQUISH Feb 01 '17 at 15:05
@SQUISH what do you mean just the row above ? you want to sum only the last row from the entire range ? – Shai Rado Feb 01 '17 at 15:06
I want to sum the cells in the sumrow directly above, right now it's summing the whole row..does that make sense? – SQUISH Feb 01 '17 at 15:10
@SQUISH you want to sum all cells in the row above ? like if I'm at Cell E3, you want to sum cells(D2:D5) ? – Shai Rado Feb 01 '17 at 15:16
what you said above is correct, sorry, I do want the sum formula at the row below the bottom row of my sumrow named range...i'll re edit my code, it's not working. – SQUISH Feb 01 '17 at 15:19
something is not working the xlDown, when i put the error over the code xlDown (=-4121)... – SQUISH Feb 01 '17 at 15:28
Range("sumrow").Offset(1, 0).Formula = "=Sum(" & Range("sumrow").Address & ")" this works but it selects my entire sumrow row, and I only want it to sum the totals direct above each cell below it, sumrow consists of 4 columns of numbers... – SQUISH Feb 01 '17 at 16:00
@SQUISH your description doesn't make much sense to me - it would be really helpful if you could amend your question with a sample of the data. – Blackhawk Feb 01 '17 at 16:24
@SQUISH please edit your post and add a screen-shot of your excel sheet, describe where is `Range("sumrow")` defined, which data you want to Sum – Shai Rado Feb 01 '17 at 17:21

VBA to AutoSUM selected Range

1 Answers1