This is an easy code but very useful, it will refresh the pivot table automatically each time I will update the data.

 

When I use the macro ?

Each time that I need to refresh a pivottable.

 

How to create the macro ?

Read How to create, edit, hide and select a macro in an excel report

 

How to create the button to associate it with the macro ?

Read How to create a button and associated it to a macro in an excel report

 

How is the macro ?

Copy the code below and paste it into your macro. You will see my comments in green if exist so follow the help to adapt to your need.

Sub test()
' change name sheet1 and pivottable1 by yours
Sheets("sheet1").PivotTables("PivotTable1").RefreshTable
End Sub

If you want to filter, you need to refresh first:

Sub test()
Sheets("sheet1").Select
' refresh the pivottable named PivotTable1
ActiveSheet.PivotTables("PivotTable1").RefreshTable
Dim var As Variant
Dim pvt As PivotTable
Dim pvtfld As PivotField
' change xxx by the filter name
var = "xxx"
Set pvt = ActiveSheet.PivotTables("PivotTable1")
' change yyy by the column name of the filter
Set pvtfld = pvt.PivotFields("yyy")
pvtfld.PivotFilters.Add xlCaptionEquals, Value1:=var
End Sub

If in the selection, the “blank” name appears and I don’t want to display it:

Sub test()
' change the pivottable name pivottable1 and month by yours
ActiveSheet.PivotTables("PivotTable1").PivotFields("Month").ClearAllFilters
ActiveSheet.PivotTables("PivotTable1").RefreshTable
Dim pvt As PivotTable
Set pvt = ActiveSheet.PivotTables("PivotTable1")
pvt.PivotFields ("Month")
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Month")
.PivotItems("(blank)").Visible = False
End With
End Sub

Now if I want to display only “win” for group:

Sub test()
' refresh the pivottable named PivotTable1
ActiveSheet.PivotTables("PivotTable1").RefreshTable
Dim var As Variant
Dim pvt As PivotTable
Dim pvtfld As PivotField
' change win by the filter name
var = "win"
Set pvt = ActiveSheet.PivotTables("PivotTable1")
' change group by the column name of the filter
Set pvtfld = pvt.PivotFields("group")
pvtfld.PivotFilters.Add xlCaptionEquals, Value1:=var
End Sub