turn off recalc in PivotTables?

[Guest]

Hello.

I have a subroutine that creates a Pivot Table which has 10 row fields.

It then goes through and turns off all of the subtotaling for the 10 row
fields.

Each time a row field's subtotal is turned off, it recalcs the Pivot Table
before moving on to removing the next total.

I woudl like for it to not recalc, but then recalc the Pivot Table only at
the end.

Is there a setting that I can turn off? I tried setting the
application.recalculation property to xlmanual, but that did not affect the
Pivot Table.

Thanks,
Mark

 

[Guest]

Mark,

It might help if you post the code that you have so far...

Perhaps this might help. Take a look at the ManualUpdate of the pivot table.
From the Excel help:

 

[Guest]

I'm pretty sure that will do it... I'll test it now.

My guess is that after setting it back to auto, I'll need to refresh the
table.. but that's all fine.

It's in a subroutine that takes an array for the row fields, an array for
the colum fields, an array for the page fields, an array for the data fields,
etc... a callable Pivot Table creator...

I think your suggestion is the answer. Thanks for it.

Mark

 

[Guest]

it turns out that the actual key is to turn off the subtotals before adding
the data fields.

the slowness was in recalc'ing as it removed the subtotls, but, it already
knows not to do subtotals before it's bothered with any data, then all is
well.

thanks