VBA Pivot Table Array?

[jimsjams]

Hi there,
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
I was wondering whether anybody knew whether it is possible to use an array to choose which pivot items to select.
<o> </o>
I have a pivot table which contains thousands of pivot items (‘sell codes’), but I would like to select only a few hundred. As I am going to have to do this daily (and the ones I am looking for might change each day) I’d like to be able to fill an array with the relevant ‘sell codes’ and get the pivot table to only display these.
<o> </o>
Is this possible using an array to populate the pivot items?
<o> </o>
If not – can you think of a better way of only displaying certain pivot field items without having to either select them from the drop down box on the pivot table or manually write the .pivotitems(“example1”).visible=true vba line for each one I want to see?
<o> </o>
Thanks very much for any thoughts,
<o> </o>
James

 

[Fazza]

Hi, Jim.

Maybe have a new table with the list of sell codes you want to see? Then put an extra column in the source data and call it "ShowIt". Populate with a formula to return TRUE or FALSE if the selll code for that record is in the new list of sell codes. Such as =ISNUMBER(MATCH(sell code, new list, 0)) Make this new field a page field in the pivot table (and set it to TRUE).

It can also be done without adding the extra column to the data but is a little more complex - such as create the pivot table via the external data option and use SQL to do the work. Such as below. Untested.

SELECT BT.*
FROM BigTable BT, NewTable NT
WHERE BT.`sell code` = NT.`sell code`

This SQL is a rough guide, if you want to take this option, you might need to post some more details. With this approach the pivot table will only have the records for which the `sell codes` are in the list of codes you want.

There are other ways via VBA but the above should be adequate.

Regards, Fazza