Pivot Table Refresh Macro Causing Memory Issues

[docm24]

Good afternoon,

I am working with a large spreadsheet that makes use of an equally large number of pivot tables. Currently the sheet has 231 pivot tables, 228 of which are all referencing the same data source. That data source is also, not surprisingly, large. The data source covers 90 columns and 10,000 rows.

The problem that I'm running into is that when I attempt to refresh the pivot tables after fresh data has been inserted, Excel runs out of memory and crashes. I wrote a macro that iterates through each table on each sheet and allows me to monitor the refresh a little better. I learned that I can refresh about 60 of the tables before I run out of memory.

As each table refreshes, I can watch the growth of the resources that Excel is using. The first three tables don't create a lot of chance, as they are based on a smaller data source. When the tables with the larger data source start refreshing, the resources are quickly eaten up.

I'm writing to see if there is a known way to free up memory resources so that I can complete the refresh of all of my pivot tables. I realize that I might be able to achieve what I'm looking for by redesigning all of the pivot tables, but I'm really hoping there's a simpler solution.

Thanks for your help,
Doc

 

[docm24]

Nothing huh. Oh well. I'm still having issues with this beast. I want to say that I read an article once saying that pivot tables that are all based on the same original table all use the same cached data to pivot on. I thought this would mean that the cache would only need to be refreshed once. Instead, my spreadsheet is refreshing for every table. It looks like they are all using the same data source as the same calculated fields show up for each even though they were only made for one set of tables. Is there something I could have done that caused a sort of disconnect among the tables? Does anyone have know of an article that reviews how Excel organizes the data that is cached for pivot tables?

Thank you,
Doc

 

[matthewjscott]

I know this is an old thread, but I am having the same issue. Is there anyone that may have an answer or fix for this?