Can I Turn PivotCache Refresh (Calc) off & on in VBA?

  • Thread starter Thread starter MikeZz
  • Start date Start date
M

MikeZz

Hi,
I have a pivot where I maniuplate the Page/Row fields through VBA.
I want to turn off all updating while I manipulate the fields because
- The pivot is huge.... 50k rows of data
- I sometimes have to move numerous field

Then I want to turn it on and refresh the pivot all at once.

Excel has to have a way to do it and I thought I saw it somewhere but can't
now.

To get an idea of what I mean
If I manually move Page Fields around one at a time, it can take a few
seconds to do each update.... completely rebuilding the pivot via VBA can
take 20 or more seconds since it refreshes the table each time.

If I right click on the pivot, use the Pivot dialog to rebuild the pivot, it
only takes a few seconds because excel only rebuilds the pivot once at the
end.

I want to do this via VBA and there has to be a way.

Thanks MikeZz
 
If PT is the Pivot Table.....

turns off auto update:
PT.ManualUpdate = true

turns on auto update.
PT.ManualUpdate = False
 
Back
Top