PivotItems updating pivottable

[elduke]

I have a workbook containing some specific clients on sheet (1) and a pivot Table on another sheet I need to update the Pivot Table with only those Clients when I refresh the Pivot Table(“SpecClient”).
Example :-

Range Name (Sheet(1)!$K$3:$K$28 )

Client 104
Client 054
Client 730
Client 062

I would like to see in Pivot Table.pivot Items only clients 104, 054, 730, 062 and eventually another one should I add another client in the existing Range. The Clients on the pivot table are on a page field.


heres an example..

With ActiveSheet.PivotTables("SpecClient").PivotFields("Name")
For CliName = 1 To Range Name
If cell is not empty then
.PivotItems(CliName).Visible = False
End If
Next CliName
End With

This works exactly the way i want but for one thing. The pivot table is being updated as soon as change one of the pivotitems visible property. This is annoying and slow. I will like to make all my changes first then have the pivot table refreshed.

I added these 2 lines to my code...

Application.EnableEvents = False
Application.ScreenUpdating = False

but its still terribly slow....
to see what im talking about... create a pivot table... add a pagefield.. record a new macro....select multiple items of the page field... and stop the macro... now run the new macro... see how many times the table is refreshed?...

can anyone help?

 

[elduke]

I also tried using CurrentPageList property which does exactly what i need...but that only works with OLAP data sources..

anyone?...

 

[elduke]

arggh!!!... let me explain my pagefield a little more.... i call it the listtype column... so each record in my table contains account #, item, period, and the listtype column.... the listtype column contains account information... such as: accounttype, pricingtype, etc... it looks like "customer;shipto;vendor;level2pricing; christmaspricing; specialpricing;"... the listype can contain up to 10 different items... i joined all the items together because creating 10 different columns will be impractical...

so my pivot table source looks something like this...

account item qtysold year listtype
12345 62-4 100 2004 WHITE;XMAS07;customer;HD007;QUOTE;LEVEL3
23451 P54-8 25 2000 customer;NOW98;BLUE;PLANX;WINX9;FREIGHT850;LEVEL1;ZCOM7NB; 21542 7754s 3025 2008 BLUE;NYSTATE;vendor;customer;LEVEL;PKG50;SPCLW205;;WBOXL30;FREIGHT900;5COMMISION; 21245 54-4Nut 500 2007 LEVEL1;XMAS08;customer;
21541 91PTC 10500 2008 customer;GREEN;level3;


and my pivot table looks something like..

--------
listtype (All)
--------
------- ----
account year
------- ----
2000 2001 2002 2003 2004
20115 544 1000 5454 825 15
21157 4545 54500 5488 100 255
58738 1000 525 1 -500 544
54588 200 300 25 400 800

on another spreadsheet ill have the listypes i want to filter my pivot table by;

listype
-----
BLUE
customer
Level3
XMas07
....

or i can just joint my filter list on a cell next to the pivotpage field... in any case... i have to iterate through the pivotitems collection and check/uncheck the visible property... the user may enter 3 or 4 different listtypes he want to filter by but for simplicity lets say theres only 1 listtype i want to filter by ("BLUE" accounts):


dim pitem as pivotitem
For pitem in pivottable.PivotFields("Listtype")
if instr(1, pitem.value, "BLUE") then
pitem.Visible = true
else
pitem.Visible = false
End If
Next CliName

... the table is recalculated as soon as i set the visible property... it will recalculate 100's of times before im through iterating through the pivotitems list...

the CurrentPageList property will allow me to do exactly what i want... i believe it can set/read the pivotpage field with an array... the problem is that it only works with OLAP data sources...

im pulling my hair out here....



...

 

[elduke]

ok... found the solution after looking thru all the pivottable objects.... i used the MANUALUPDATE property of the pivottable....

thanks for all your help..