Simple Subtotal

[jdenys]

Hi, I am having difficulties with a subtotal formula in a macro that i need. My spread sheet looks like this



Material Price Quantity
AAAA 10.34 5
BBBB 3.45 7
CCCC 7.55 12

I would like to put a subtotal at the bottom of Price as well as Quantity. I need this to either take the total number of rows, or look at the material row to find the bottom whichever is easier to do as my spreadsheets will have different amounts of rows.

Thank You!!!

[oeldere]

You could use pivot table for that.

[jdenys]

Hi Oeldere, i need to include this part in a macro that I have built already.

[oeldere]

why don't you record the manual actions, with the macro recorder.

then you have the code.

[jdenys]

'
Columns("A:A").Select
Selection.End(xlDown).Select
Range("A7").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-6]C:R[-1]C)"
Range("A8").Select
End Sub


How can I guarantee that it will pick up everything in the entire column because on each tab i have different amounts of rows

[stnkynts]

I would try this on a test copy of you workbook since i didn't have specifics on locations of data. Should work across all worksheets. If you only want specific worksheets you will need to alter code.

Sub Subtotals()
Dim wksht As Long, lastrow As Long

For wksht = 1 To Worksheets.Count
    lastrow = Sheets(wksht).Range("A" & Rows.Count).End(xlUp).Row
        If Not lastrow < 2 Then
            Sheets(wksht).Range("B" & lastrow + 2).FormulaR1C1 = "=SUM(R1C2:R" & lastrow & "C2)"
            Sheets(wksht).Range("C" & lastrow + 2).FormulaR1C1 = "=SUM(R1C3:R" & lastrow & "C3)"
        End If
Next wksht

End Sub

[jdenys]

Thank you Stnkynts!!!