Sum Table Column Values using .TotalsCalculation = xlTotalsCalculationSum

Raddle

New Member
Joined
Oct 24, 2023
Messages
41
Office Version
  1. 2016
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
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
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.

:)
 
Upvote 0
Solution

Forum statistics

Threads
1,223,098
Messages
6,170,103
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top