As I’ve mentioned before – if you’re working with a single dataset, the ideal scenario would be that your spreadsheet doesn’t contain pivot tables with different pivot caches.
The ideal scenario is not always possible, however. Sometimes you can’t avoid pivot tables that have different pivot caches as your analysis might need to draw on information that comes from different datasets. Whether the data comes from different tables in a single database or different databases altogether, it will always result in two different pivot caches (unless you do some data prep in PowerPivot or in a separate file where you merge all the datasets you need)
How do you refresh then?
You’ll need a loop in any case.
One way would be to simply loop through the different pivot caches in your workbook and refresh these.
The second option would loop through all the pivot tables and refresh each one of them.
Even though both approaches will ultimately produce the same end result, one might be preferable over the other depending on your spreadsheet setup.
For example – if your spreadsheet contains a couple of tables that share the same pivot cache, it might be more efficient to refresh the caches instead of looping through the pivot tables. This way you will not be refreshing the same pivot cache over and over again.
Happy VBA coding!