Sum Table Column Values using .TotalsCalculation = xlTotalsCalculationSum

[Raddle]

Hi

Can anyone help with how to call the above function properly ?

Dim ws As Worksheet
Dim tCells As Ranges
Dim tcell As Range

On Error Resume Next
For Each ws In ActiveWorkbook.Sheets
ws.Activate ' I know some folk say you don't need this, but I always seem to

For Each tbl In ws.ListObjects
If tbl.name <> "Tbl_Testertable" Then

' loop through columns applying totals

For Each tcell In tbl.HeaderRowRange.Cells

If tcell.Value Like "*Currency*" Then
tcell.Offset(1, 0).Select
Range(Selection, Selection.End(xlDown)).Select

With Selection
.Style = "Comma" ' this appears to be working nicely
.ShowTotals = True ' not sure if this bit is working or not
.TotalsCalculation = xlTotalsCalculationSum' this bit definitely not working
End With
End If
Next tcell

End If

Next tbl

Next ws
End Sub

 

[Raddle]

In pain here ..

So the only way I could find to make this work was, surely a very long way indeed and that is to get the header rows names and push them in to a loop.

Sub AddSumToColumns()
'
Dim ws As Worksheet
Dim tbl As ListObject
Dim tcell As Range

For Each ws In ActiveWorkbook.Sheets
ws.Activate
' Debug.Print ws.name

For Each tbl In ws.ListObjects
' Debug.Print tbl.name

If tbl.name <> "Tbl_mytablehere" Then

For Each tcell In tbl.HeaderRowRange.Cells
' Debug.Print tcell.Value

If tcell.Value Like "*GBP*" Then
' Debug.Print tcell.Value

T = tcell.Value
Debug.Print T
ActiveSheet.ListObjects(1).ListColumns(T).TotalsCalculation = xlTotalsCalculationSum ' this is the line I could not shorten - 'with rng' didn't work etc


End If
Next tcell

........

Of course if there were more than one table on the sheet, this would only handle the first one but in my case that is ok.

If there is a neater way to do this then grateful to be schooled.