The Activate event is triggered whenever you make that worksheet active, so in order to test that the procedure is working correctly you need to perform the event.
Return to the Excel workspace by clicking an Excel icon or by pressing ALT+F11, select another worksheet in the workbook and then return to the original worksheet where the Pivot Table is stored. This triggers the event and you will see that the tables are automatically refreshed. The best test is to change something in your source data and then see if your Pivot Table updates to reflect the change.
Using your Macro Again
Of course, you do not have to do the macro recording every time, all you need is the code for the Refresh method so you can type it in or paste it from a previous recording. But, be careful, each Pivot Table needs to be correctly referenced using its Name Property, this is the text value inside the brackets. In the example below the name of the Pivot Table is “PivotTable1”
ActiveSheet.PivotTables(“PivotTable1”).PivotCache.Refresh
To find out the name of a specific Pivot Table, right-click one of its cells and choose Pivot Table Options from the short cut menu.