Return Subtotal for Table Columns

[manster_mg]

I have an excel file that has been converted into a table.
I tried to use the code below to have subtotals returned at the bottom of the table.
It did not work. How can I fix this?



Range("Table1[[#Totals],[DS Fcst]]").Select
ActiveSheet.ListObjects("Table1").ListColumns("DS Fcst").TotalsCalculation = _
xlTotalsCalculationSum

[Aussiebear]

Use this as a guide and adjust as necessary


Private Sub cmbSummarizeColumns_Click()
Dim the_sheet As Worksheet
Dim table_list_object As ListObject
Dim tblCOL As Long
Set the_sheet = Sheets("Sheet6")
Set table_list_object = the_sheet.ListObjects(1)
With table_list_object
If Not .ShowTotals Then .ShowTotals = True
For tblCOL = 1 To 3 'might use To .DataBodyRange.Columns.Count
.TotalsRowRange.Cells(1, tblCOL) = Application.Sum(.DataBodyRange.Columns(tblCOL))
Next tblCOL
End With
End Sub

[manster_mg]

Hi Aussiebear. I was able to adjust the code for my file and get it to work. I wasn't sure what the Dim tblCOL was being called for but now I realize I did not specify that I only want subtotals for columns F, G, M, Q, R, and S (all have headers). Also for column AA I want to call the MAX function in place of Subtotal. How can I adjust the If Not Next argument to only return those specific columns or will I need to use a different code? What can I use in place of .ShowTotals to return the MAX function value? Thank you

[manster_mg]

For anyone following, I found the solution below for Table column totals.
You will substitute the .ListColumns "Names" with your own header or use (ColumnN) where N = column number.
It works nicely.



Sub TableTotalRow()Dim wrksht As Worksheet
Dim ObjListObj As ListObject
Set wrksht = ActiveWorkbook.Worksheets("WOS")


Set ObjListObj = wrksht.ListObjects(1)


ObjListObj.ShowTotals = True
With wrksht.ListObjects("Table1")
.ListColumns("Fcst").TotalsCalculation = xlTotalsCalculationSum
.ListColumns("OH").TotalsCalculation = xlTotalsCalculationSum
.ListColumns("OO").TotalsCalculation = xlTotalsCalculationSum
.ListColumns("TTL P").TotalsCalculation = xlTotalsCalculationSum
.ListColumns("TTL P $$").TotalsCalculation = xlTotalsCalculationSum
.ListColumns("SOQ").TotalsCalculation = xlTotalsCalculationSum
.ListColumns("SOQ $$").TotalsCalculation = xlTotalsCalculationSum
.ListColumns("LT").TotalsCalculation = xlTotalsCalculationMax
End With


End Sub