Autorefresh Pivot table

[gazmoz17]

Hi,

I have followed this great article/video however I am getting an error. https://www.myonlinetraininghub.com/auto-refresh-pivottables Point 6 of article at the bottom.

I'm guessing is the error because this VBA code will only work on workbooks with 1 source and all pivot tables poinitng to said data source "YE 20 onwards". Rather than my workbook with multiple data sources and linked pivot tables?

Is there an alternate code please that will refresh all pivot tables

Many Thanks
Gareth

 

[Alex Blakenburg]

Why not just use

ThisWorkbook.RefreshAll

 

[gazmoz17]

Your kidding haha Im relatively new to vba and first time using it in context of pivot tables.

The above was just what I stumbled across.

Where in the VBA editor do I put the code please?

 

[Alex Blakenburg]

Where in the VBA editor do I put the code please?

Unfortunately that depends on when you want the refresh to run.
If you had all your data on one sheet you could trigger put it in the Worksheets code module and run it using Worksheet_Deactivate as in your example.
But you are saying you have multiple sheets that have data. If you have all your pivots on 1 sheet you could run it in that worksheets module using Worksheet_Activate.

If both data and pivots are on multiple pages then you need to either run it much more often eg in the ThisWorkbook code module using the Workbook_SheetDeactivate (or Workbook_SheetActivate) OR have a button that the user needs to press.

Note: If you are using Power Query to Populate a table which in turn populates a Pivot table you will need to turn off the Background Refresh for the queries that do that (or for all queries in that workbook.)
Disable Background Refresh on All Power Query Connections - VBA

 

[gazmoz17]

Hi Alex,

Thanks for that really appreciate your time. Will definitely come back to you with some questions after Ive digested the options and also the best way of achieving what I want to achieve . On dreaded work stock check at the moment but as soon as Im free I want to drill down on this. Thanks the extra detail I came across your last point “Disable Background Refresh” yesterday on youtube, so that part makes sense to me.

Thanks