Sum unknown rows with know columns

[msquared99]

OK, I'm a bit bumfuzzled here. I am new at this. Here is what I have. In coulmns O thru Z are total sales for each month by rep. The number of rows can change (be more or less). My data will always start in cell O2 thru Z2, but I do not know which row it will end with. Columns A thru K contain the raw data and the below macro is summing it up.

I want to be able to add a sum for each month at the bottom of the data. This month it may be row 27, next month it may be row 30 or 25.

Here is the line causing me the issue:
Range("N" & EndRow + 1 : "Z" & EndRow +1).FormulaR1C1 = "=Sum(R"[EndRow +1]"C:R[-1])"

I get a Compile Error Expected: List separator

All I want is to add a sum to each known column but to a variable row in columns O thru Z.

Thanks,

Here is the entire macro:
Sub RepsRevenue()
Dim IRange As Range
Dim Orange As Range

finalrow = Cells(Rows.Count, 1).End(xlUp).Row
nextcol = Cells(1, Columns.Count).End(xlToLeft).Column + 4

Range("H1").Copy Destination:=Cells(1, nextcol)
Set Orange = Cells(1, nextcol)
Set IRange = Range("A1").Resize(finalrow, nextcol - 4)

IRange.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Orange, Unique:=True

lastrow = Cells(Rows.Count, nextcol).End(xlUp).Row

Cells(1, nextcol).Resize(lastrow, 1).Sort key1:=Cells(1, nextcol), order1:=xlAscending, header:=xlYes

Cells(1, nextcol + 1).Value = "Revenue"
Cells(1, nextcol + 2).Value = "1"
Cells(1, nextcol + 3).Value = "2"
Cells(1, nextcol + 4).Value = "3"
Cells(1, nextcol + 5).Value = "4"
Cells(1, nextcol + 6).Value = "5"
Cells(1, nextcol + 7).Value = "6"
Cells(1, nextcol + 8).Value = "7"
Cells(1, nextcol + 9).Value = "8"
Cells(1, nextcol + 10).Value = "9"
Cells(1, nextcol + 11).Value = "10"
Cells(1, nextcol + 12).Value = "11"
Cells(1, nextcol + 13).Value = "12"
Cells(1, nextcol + 14).Value = "Total"
Cells(2, nextcol + 1).FormulaR1C1 = "=sumif(r2c8:R" & finalrow & "c8,rc[-1],r2c7:r" & finalrow & "C7)"

If lastrow > 2 Then
Cells(2, nextcol + 1).Copy Cells(3, nextcol + 1).Resize(lastrow - 2, 1)
End If

Range("J2:J" & finalrow).FormulaR1C1 = "=VLOOKUP(RC[-1],Date,2,0)"
Range("K2:K" & finalrow).FormulaR1C1 = "=RC[-3]&RC[-1]"

EndRow = Cells(Rows.Count, 13).End(xlUp).Row

Range("O2:Z" & EndRow).FormulaR1C1 = "=SUMIF(R2C11:R" & finalrow & "C11,RC13&R1C,R2C7:R" & finalrow & "C7)"

Range("N2:AA" & EndRow + 1).NumberFormat = "$#,##0_);[Red]($#,##0)"

Range("N" & EndRow + 1 : "Z" & EndRow +1).FormulaR1C1 = "=Sum(R"[EndRow +1]"C:R[-1])"

Range("AA2:AA" & EndRow + 1).FormulaR1C1 = "=SUM(RC[-12]:RC[-1])"

Columns("N:AA").ColumnWidth = 11

Rows("1:1").Select
With Selection
.Font.Bold = True
.HorizontalAlignment = xlCenter
End With

Columns("AA:AA").Font.Bold = True


End Sub

[VoG]

Perhaps like this



Range("N" & EndRow + 1 & ":Z" & EndRow + 1).FormulaR1C1 = "=Sum(R[" & EndRow + 1 & "]C:R[-1])"

[Bob Phillips]

Don't you just want

Cells(EndRow + 1, "N").Resize(, 13).FormulaR1C1 = "=Sum(R1C:R[-1]C)"

[msquared99]

VoG,

That worked perfect!

xld,

I tried yours but got a circular reference.

Thanks for the help!

[msquared99]

Ooops,

Had it wrong.

xld's worked perfect.

VoG's had the circular reference.