VBA for Pivot Table -- Select All, then deselect values = 0

[bisel]

Am trying to create a macro using VBA that will select a pivot table's primary filter (Years Total) and select All. Then once selected, de-select the values = 0. I tried to do this by recording a macro but i get the following code when I record the macro ...

Sub pivottable_refresh()
    
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Years Total").CurrentPage = "(All)"

End Sub

... which does not work.

Why I want this? Because in my workbook, data can change by adding additional data elements. Unless I manually refresh the pivot table, it is likely that it will not reflect the current data.

Appreciate any help.

Regards,

Steve

 

[jasonb75]

This is what I get from the macro recorder by following the same steps that you describe.

Sub pivottable_refresh()
    
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Years Total").PivotItems("0").Visible = False

End Sub