Turning off Pivot Subtotals

[jomili]

I've got a massive pivot table, created by out IT department, that I programmatically rearrange for my needs. I use my old tried and true methods for all of my operations, and everything works well, until we come to turning off the subtotals. My full macro is shown below, and the two methods I've used to turn off subtotals. Just that small piece of code takes over 1/2 hour to run (I keep killing it after 1/2 hour, I don't have that much time to kill), WITH calculation, screenupdating, etc. turned OFF (by the Speedon macro), manual update turned ON.

I think the problem may be that we're running through every PivotField. This pivot is huge, pulling date from a data warehouse. So, I wonder if there's any easy way to just turn off the subtotals for the PivotFields that I'm actually using in my pivot. Any help I can get on that would be appreciated.

BTW, the "STOP" sections in the code were just so I could spot where my bottleneck is.

Please Login or Register  to view this content.

[jomili]

Well, it looks like it might NOT be my subtotal routine. I added msgboxes (ie "Added Monetary Amount") to check my progress through the macro. I complete all operations except .ManualUpdate = false. I get TO it, but not THROUGH it. I tried it manually doing each step, and the pivot works fine. Any ideas?

[rorya]

FYI I’ve always found automating the menu item to turn off sub totals to be much faster (using commandbars.executemso)

If you comment out the sub totals parts, does the rest run smoothly?