VBA and Pivot Table Refresh

[alondon]

Folks,

I posted this question about 3 weeks ago but got no response - probably
because I didnt make it clear what the problem was.

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 (Include or Do
Not Include) using VBA, 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). But, I don't want the
Pivot Table to refresh for each change as it takes about 10 seconds each
time. I want to make the 12 changes, then refresh the table - the same way
it works when making changes on the screen in Excel.

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
(e-mail address removed)

 

[bz]

Folks,

I posted this question about 3 weeks ago but got no response - probably
because I didnt make it clear what the problem was.

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
(Include or Do Not Include) using VBA, 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). But, I don't
want the Pivot Table to refresh for each change as it takes about 10
seconds each time. I want to make the 12 changes, then refresh the
table - the same way it works when making changes on the screen in
Excel.

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?

Have you tried
Application.EnableEvents = False
.... do your things
Application.EnableEvents = True




--
bz

please pardon my infinite ignorance, the set-of-things-I-do-not-know is an
infinite set.

(e-mail address removed) remove ch100-5 to avoid spam trap