Refresh All & Pivot Tables

[bradams]

Hi. I have a spreadsheet with probably three dozen pivot tables spread across multiple sheets. I am sure there is a more efficient way of doing it. I've never gotten around to learning PowerPivot or macros or whatever else I would need to use, so I use what I know. This spreadsheet gets updated with fresh data every month. The problem I run into is with making sure all the pivot tables are updating with the fresh data. I use Refresh All but it seems to be hit or miss. I have some manual checks built in to see if numbers from one data set add up to match another data set (for example, one data source might be a list of schools attended and another data source might be the kid's zip code, so both lists should have an equal number of entries). When those don't match, I often find all I have to do is manually go to the worksheet with the malfunctioning pivot table and hit Refresh again. I don't know why Refresh All isn't consistently working across the entire workbook. Any thoughts?

 

[Fazza]

maybe some data is dependent on other data, in which case a single refreshall won't suffice.
if one table used data from another table, then refreshall would need to be done twice.
if one table used data from another table that itself depended on another table, then refreshall would need to be done thrice.

if not, then I don't understand either. however this might fix it - use VBA to loop through each worksheet and within them each pivot table, refreshing every one

like this Refreshing all the pivot tables in my excel workbook with a macro

note, if you have one table dependent on the results of another table, this does not specifically address that. if you have that, then either refresh in the required order or multiple times