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
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
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.
We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel
Which adblocker are you using?
Disable AdBlock
Follow these easy steps to disable AdBlock
1)Click on the icon in the browser’s toolbar. 2)Click on the icon in the browser’s toolbar. 2)Click on the "Pause on this site" option.
Go back
Disable AdBlock Plus
Follow these easy steps to disable AdBlock Plus
1)Click on the icon in the browser’s toolbar. 2)Click on the toggle to disable it for "mrexcel.com".
Go back
Disable uBlock Origin
Follow these easy steps to disable uBlock Origin
1)Click on the icon in the browser’s toolbar. 2)Click on the "Power" button. 3)Click on the "Refresh" button.
Go back
Disable uBlock
Follow these easy steps to disable uBlock
1)Click on the icon in the browser’s toolbar. 2)Click on the "Power" button. 3)Click on the "Refresh" button.