VBA and PivotTable refresh
 

Folks,

I need to build a very complex report using on about 30,000 records using
Pivot Tables. I got everything to work UNTIL I tried selecting (and
de-selecting) items within a field. Each time a field is set the Pivot
Table recomputes. Depending on the month selected by the user, I need to
set 12 items in the MONTH field to True (include) or False (don't include).
I don't want the Pivot Table to refresh 12 times as it takes about 10
seconds each time!

I have already tried .Pivot.EnableRefresh = False - that only turns off
manual refresh. I tried placing multiple items in a .PivotItems statement
(e.g. .PivotItems("10/1/2007", "11/1/2007").Visible = True), but only a
single argument is allowed.

Bummer! I am was so close to creating something really cool. What am I
overlooking here? Anybody have any ideas?

Thanks for your help,

Allan P. London, CPA

VBA and PivotTable refresh
 

Turn off ManualUpdate at the start of the code, and on at the end:

ActiveSheet.PivotTables(1).ManualUpdate = True
'your code
ActiveSheet.PivotTables(1).ManualUpdate = False


alondon wrote:
Folks,

I need to build a very complex report using on about 30,000 records using
Pivot Tables. I got everything to work UNTIL I tried selecting (and
de-selecting) items within a field. Each time a field is set the Pivot
Table recomputes. Depending on the month selected by the user, I need to
set 12 items in the MONTH field to True (include) or False (don't include).
I don't want the Pivot Table to refresh 12 times as it takes about 10
seconds each time!

I have already tried .Pivot.EnableRefresh = False - that only turns off
manual refresh. I tried placing multiple items in a .PivotItems statement
(e.g. .PivotItems("10/1/2007", "11/1/2007").Visible = True), but only a
single argument is allowed.

Bummer! I am was so close to creating something really cool. What am I
overlooking here? Anybody have any ideas?

Thanks for your help,

Allan P. London, CPA






--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html