Hi Dhregan,
You can use VBA e.g., construct a macro to refresh to pivot table and call a second macro that resets the filter.
For instance: in a module, (hit: alt F11 and in the vba editor; select from the Insert drop-down: "module" ). Paste the below code into the module window, taking care to substitute the example pivot table name (YourPivotTableName) with the name of your actual Pivot Table!
VBA Code:
Sub RefreshPivotMacro()
Dim pt As PivotTable
Set pt = ActiveSheet.PivotTables("YourPivotTableName")
pt.RefreshTable
Call ResetFilter
End Sub
In order to construct the ResetFilter macro, you need to consider the question of: whether 'Your Field Name' is in the
Report Filter or
in the Column or Row Labels.
If 'Your Field Name' is in the Report Filter, add the below macro beneath your RefreshPivotMacro:
VBA Code:
Sub ResetFilter()
Application.ScreenUpdating = False
ActiveSheet.PivotTables("YourPivotTableName").ManualUpdate = True
ActiveSheet.PivotTables("YourPivotTableName").PivotFields("Your Field Name").ClearAllFilters
ActiveSheet.PivotTables("YourPivotTableName").PivotFields("Your Field Name").CurrentPage = _
"Yes"
ActiveSheet.PivotTables("YourPivotTableName").ManualUpdate = False
Application.ScreenUpdating = True
End Sub
If 'Your Data Field' is in the Column or Row Labels, instead add the below macro beneath your RefreshPivotMacro:
VBA Code:
Sub FilterPivotTable()
Application.ScreenUpdating = False
ActiveSheet.PivotTables("YourPivotTableName").ManualUpdate = True
ActiveSheet.PivotTables("YourPivotTableName").PivotFields("Your Field Name").ClearAllFilters
ActiveSheet.PivotTables("YourPivotTableName").PivotFields("Your Field Name").PivotFilters. _
Add Type:=xlCaptionEquals, Value1:="Yes"
ActiveSheet.PivotTables("YourPivotTableName").ManualUpdate = False
Application.ScreenUpdating = True
End Sub
Then you can either create a form control button on your Pivot Table Worksheet (ws) e.g., in the Developer Tab, go to >>Insert-->Form Controls-->>Button (Form Control). You then right-click the form control button and "Assign Macro" e.g., select: RefreshPivotMacro.
Alternatively, you can set the pivot table to automatically refresh: by pasting the
RefreshPivotMacro into the Worksheet window instead of the module window i.e., in VBA editor e.g., paste into Sheet2 (PivotTable) instead of Module 1.
If you do this, you have to modify the macro to:
VBA Code:
Private Sub Worksheet_Activate()
Dim pt As PivotTable
Set pt = ActiveSheet.PivotTables("YourPivotTableName")
pt.RefreshTable
Call ResetFilter
End Sub