VBA Sum R1C1 copy down

[xcelnovice]

Hi have the below code & want to manipulate it to copy down to the end of my data. Right now it goes down to Q3500 but I’d like it to be dynamic. Also if the structure of my code is funky m, mainly the last With statement I’m open to suggestions.

Sub Test()
Dim startRow As Long
Dim lastRow As Long

    startRow = 2

    lastRow = Cells(Cells.Rows.Count, 1).End(xlUp).Row

    ThisWorkbook.Worksheets("Page1_1").Cells.Copy

    With ThisWorkbook.Worksheets("DATA")
        .Range("A1").PasteSpecial Paste:=xlPasteValues

        With .Range("A4:C" & lastRow)
            .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
            .Value = .Value
        End With
    End With

With ThisWorkbook.Worksheets (“DATA”)
         .Range(“Q4”).FormulaR1C1 = “SUM(RC[-12]:RC[-1])”
End With

End Sub

 

[Joe4]

I am always leery about trying to figure out the details of what someone is trying to do by trying to decipher their code, especially when there code isn't exactly as they want it to (I don't see row 3500 hard-coded in your code anywhere, so I don't understand why it would stop there).

So, can you give us a detailed explanation in plain English of the structure of your data (being specific, regarding columns, rows, etc), and exactly what you would like the code to do?

 

[xcelnovice]

I apologize. Right now the formula only sums for 1 cell (Q4). I’d like to figure out how to change it so it would include all the rows (Q4 to the End of Data), which is currently down to Q3500. E-P is the months of the year & I am totaling them in Q. As far as I know the only part of the code that needs altered is below.

        With ThisWorkbook.Worksheets("DATA")
            .Range("Q4").FormulaR1C1 = "=SUM(RC[-12]:RC[-1])"
       
        End With

The above code sums data in E-P & puts the total in Q4. I’d like the code to do this in Col Q all the way down the sheet until the last row where there is data which is currently 3500 but will change. Hope this helps and I very much appreciate your help.

A B C D E-P Q
1

2

3

4 =SUM(E4:P4)

 

[Joe4]

It sounds like this may be what you are looking for:

        With ThisWorkbook.Worksheets("DATA")
            .Range("Q4:Q" & lastRow).FormulaR1C1 = "=SUM(RC[-12]:RC[-1])"
        End With

 

[xcelnovice]

Exactly what was trying to accomplish . Thank you for helping me move 1 step closer to being a fisherman.

 

[Joe4]

You are welcome.
Glad I was able to help!