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

bisel

Board Regular
Joined
Jan 4, 2010
Messages
152
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 ...

VBA Code:
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
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,002
Office Version
  1. 2019
Platform
  1. Windows
This is what I get from the macro recorder by following the same steps that you describe.
VBA Code:
Sub pivottable_refresh()
    
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Years Total").PivotItems("0").Visible = False

End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,114,086
Messages
5,545,877
Members
410,711
Latest member
Josh324
Top