View Full Version : [SOLVED:] Return Subtotal for Table Columns
manster_mg
10-02-2023, 02:07 PM
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
10-02-2023, 07:45 PM
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
10-03-2023, 06:50 AM
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
10-05-2023, 11:12 AM
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.