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

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