• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need Advice: Refreshing 150+ pivot tables without crashing Excel

S

SteveDave

New Member
I have been tasked with updating a macro in Excel 2007 that needs to refresh over 150 pivot tables, whose data source is approx. 65000 rows x 30 columns. File format is .xlsb. My problem is that a typical "RefreshAll" function uses up too much memory and crashes Excel. I tried looping through the pivot tables in each worksheet in the workbook and updating the data source (on a separate occasion I also tried updating each pivot cache in the loop), but that also crashes Excel. Previously, the "RefreshAll" function worked, but before the quantity of pivot tables grew exponentially.

While this workbook will be re-done in the near future to fix the fact it's horribly inefficient, I need a temporary band-aid fix but cannot seem to be able to mass-refresh these pivot tables without Excel giving up about 2/3rds of the way through the pivot tables.

What I'm hoping for: does anyone have any suggestions for a strategy to approach to make this work? I have never worked with so large a set of pivot tables before that Excel can't handle the memory load.

Thanks,

Stefan F.
Winnipeg, MB, CAN
 
HI

for clarity are you saying that the 150 Pivot tables all use the same source of 65k*30 ? If so they should be using a single cache and I'm not sure the volume of Pivots themselves should have TOO much impact. but clearly 150 is extreme. I'm intrigued how you can have 150 "pictures" on only 30 columns !

I'd be tempted to paraphrase : "I not sure you want to go there from here !" Probably a Brit expression so ignore :)

Gerry
 
Yes, all pivot tables use the same source, but if they were created separately (as opposed to one PT being copy-pasted), then they'd all use different caches, right? Perhaps pointing to the same data, but separate nonetheless.

Don't even get me started on how this spreadsheet got so out of hand. I bet 90% of these pivot tables are redundant/not even looked at. That said, I didn't create it but have been assigned to fix it. I've aged 5 years in the last 3 days trying to get this to work.
 
You could try this.
debug.print ActiveWorkbook.PivotCaches.count
if already nice and low ( one ) .. then you are right and I don't have a solution.
IF MANY ( 150 even ) then try
Giving the source range a name, writing a quick routine to to sweep all Pivots source to that range.
check the cache count again and try a refreshall.


Gerry
 
BTW remember that Excel holds on to memory when used too much. Close all excel and re-open before doing 'heavy' stuff

Gerry
 
So you were right (I learned something), the pivot cache count is 1. So why would it crash Excel? In that light, I'm still facing the same issue, is there any way I can go about refreshing all pivot tables through the single cache without overloading the memory?
 
SO. When you refresh just ONE pivot manually ... does it die ?
I suspect it will because that act should refresh ALL pivots if they share a Cache
worth try in case the refreshall is seeing some database or Querytable you dont know about!

Gerry
 
Is that fails ( remember my re-boot excel hint ) then you could go the other way. Create a SECOND range and put half of your pivots on that and then refresh a single Pivot of each source .

either way.. have a good weekend ( I'm on GMT here :) )
Gerry
 
Refreshing just the one pivot table doesn't lead it to crash, but it does reserve enough resources to not display the workbook properly. However a "Save" method is launched thereafter, so as long as the file saves and Excel can be closed/re-opened, that should be good enough. Thanks for the help!
 
Hi,

Check with this if not the data is external!

Code: 
Sub RefrshAllPT()
Dim PT As PivotTable, ws As Worksheet

Application.ScreenUpdating = False

For Each ws In ThisWorkbook.Worksheets
    For Each PT In ws.PivotTables
        PT.RefreshTable
    Next PT
Next ws

Application.ScreenUpdating = True
End Sub

Hope! xl doesn't got crashed.
 
There is an option in Pivot table "Refresh All" Pivot table when Opening File.

Go to Pivot table Option > Click on this Option > save and close this file > re-open file.

Try to do this hope it will work.

Regards
Istiyak
 
I would try refreshing them individually. Depending on how if the pivot tables are spread across multiple worksheets, deepak's solutions should help.
 
You must log in or register to reply here.
Back
Top