XL2007

I do a lot in Excel and with VBA, but I haven't had much need for pivot
tables- I very rarely put one together, but most of the time I'm already
crunching data in VBA anyway, and it is easier for me to just do data
summaries via code.

I've been asked to help with a workbook that was created by someone else,
and it involves a much more complex pivot than anything I've worked with.
I've been told that Excel crashes when trying to include every product (and
all the related detail) but it can show one product at a time without problem
(there are about 8000 products total, each with several lines of detailed
results).

I've been asked to use my VBA skills to increment through each product (a
filter field at the top of the pivot table) and after each one is selected
(and the query runs), to copy the resulting output lines to create one
(static) master list on Sheet2.

1. What is the best way to increment through individual filter values in a
filter field? Note: there are enough individual values that the filter field
shows the warning "Not all items showing"

2. After each increment, what event can I monitor to determine when the
query has finished processing?

This just hit my desk, and I've been asked to have the output by end of day,
so any help would be greatly appreciated! Otherwise, I'll be doing a lot of
copy/paste...

Thank you!!
Keith

Ok, I've actually got it all working now, but I have a related question.

Each time I update the product field, it triggers a new query to the
external OLAP data source. There aren't any formulas in the workbook, so I
haven't turned of screenupdating or calculation. Each query takes about 3
minutes; is there anything on the Excel side I can do to speed it up, or is
that 100% dependent on the query itself and the speed of the server that is
processing the query?

Thank you,
Keith

"ker_01" wrote:

XL2007

I do a lot in Excel and with VBA, but I haven't had much need for pivot
tables- I very rarely put one together, but most of the time I'm already
crunching data in VBA anyway, and it is easier for me to just do data
summaries via code.

I've been asked to help with a workbook that was created by someone else,
and it involves a much more complex pivot than anything I've worked with.
I've been told that Excel crashes when trying to include every product (and
all the related detail) but it can show one product at a time without problem
(there are about 8000 products total, each with several lines of detailed
results).

I've been asked to use my VBA skills to increment through each product (a
filter field at the top of the pivot table) and after each one is selected
(and the query runs), to copy the resulting output lines to create one
(static) master list on Sheet2.

1. What is the best way to increment through individual filter values in a
filter field? Note: there are enough individual values that the filter field
shows the warning "Not all items showing"

2. After each increment, what event can I monitor to determine when the
query has finished processing?

This just hit my desk, and I've been asked to have the output by end of day,
so any help would be greatly appreciated! Otherwise, I'll be doing a lot of
copy/paste...

Thank you!!
Keith