[RESOLVED] [Excel VBA] Refresh all pivot tables 2

[ActSciMan]

I know the code

ThisWorkbook.RefreshAll

should refresh all pivot tables in my workbook. But, it's not working. I have a big range in one worksheet, A1:IC2189 currently though the number of rows is variable. I do a bunch of stuff in the program and then the last few steps are to write in a bunch of data to the range A1:IC2189 and then immediately after that I try the above code to refresh all pivot tables. But, it doesn't refresh them all. Afterword, if I click in a pivot table and do PivotTable Tools/Options/Refresh/RefreshAll, it seems to then refresh all pivot tables. And, if I use the macro recorder when I do that, it gives me the code

ActiveWorkbook.RefreshAll

so I assume the ThisWorkbook version should also work just fine. Is there some sort of issue about refreshing right after the data is written? That is, maybe the data hasn't updated enough for the pivot table to see it?

Thanks for any help

[ActSciMan]

This is resolved. I just had calculation off and when I turned it back to Automatic before refreshing, it worked properly... my bad.

But, here's something I learned along the way. I searched for how to figure out how to refresh pivot tables and all I ever found were ThisWorkbook.RefreshAll and nested for loops going through each pivot in each worksheet to refresh every single table. The first method has the drawback that it refreshes everything, including connections, which maybe I don't need to do. The second has a huge drawback in that it refreshes the same pivot cache multiple times (probably). For example, in my program, there are 2 pivot caches and 46 pivot tables, so 44 redundant refreshes if you do it by table. So, the best way to do this is:

For Each pvtCache in ThisWorkbook.PivotCaches
    pvtCache.Refresh
Next pvtCache