Run only for filtered PivotItems

Question

I want to summarize the DataRange.Value some PivotItems of PivotTable that meets certain criteria, and I will not do this every time a PageFilter changes. Here is my code:

 Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable) Dim pt As PivotTable, pi As PivotItem, q as Double Set pt = ActiveSheet.PivotTables(1) q = 0 For Each pi In pt.PivotFields(1).PivotItems If pi.Name = someCriteria Then q = q + pi.DataRange.Value End If Next pi End Sub 

The code runs every time the PT changes (obviously) and works very well until the PageFilter hides the element matching someCriteria , and then 1004 Error appears, given that it cannot retrieve the DataRange pi property.

Two questions:

(In this context) Is there a way to run the code only when the PageFilter changes (or should this be done with the Worksheet_Change event)?

How do I execute my For-Loop only on filtered items?

I figured out a solution that does some error handling, but I guess there should be a more elegant way to do this.

Thanks.

Answer 1

I can not reproduce your mistake. If I create a simple pivot table with the elements of the page "Test" and "Test2" and change the filter of the page so as not to show "test", I do not get error message 1004. This is with someCriteria equal to "Test". I get a 13 Type Mismatch error message, regardless of whether the "Test" is hidden or not, because it is trying to add a range to q.

The line selected by the debugger is "q = q + pi.DataRange.Value."

Note that the address pi.DataRange does not really depend on whether the test is selected in the page filter. For example, in the picture, Test2 is selected, and the parameter pi.DataRange.Address is B4: B5.

So, if I changed the code to the following, it starts without errors, but it leads to 3, although Test is not selected:

 Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable) Dim pt As PivotTable, pi As PivotItem, q As Double Dim someCriteria As String Dim cell As Excel.Range someCriteria = "test" Set pt = ActiveSheet.PivotTables(1) q = 0 For Each pi In pt.PivotFields(1).PivotItems If pi.Name = someCriteria Then For Each cell In pi.DataRange q = q + cell.Value Next cell Debug.Print q End If Next pi End Sub 

Since I cannot duplicate your initial success or specific failure, I clearly do not quite understand. Perhaps with more detailed information about the structure of your pivot table I could be.

I can say that if you want to commit only changes to the page filter, you need to encode the Worksheet_Change event, as you suspected.