Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ShowDetail on PivotField rather than PivotItem??
Hello all,
I'm trying to run a routine on the pivot update event which goes through the pivot table and hides detail wherever a particular pivot item shows. The pivot item indicates unknown/misc classification and so by removing the detail at this level you know for certain that the aggregation is MECE (mutually exclusive and collectively exhaustive) where as when the unknown category level of detail shows this isn't the case because some of aggregate for the other pivot items at that level could be in the unknown/misc category. My explanation maybe confusing but its unnecessary to solve my problem. The first stage of the code shows all details to ensure that everything is being considered. When I originally developed the code it worked great however it was on a small dataset. Now I have a much larger dataset and so I've been trying to make the code more efficient. I've already done the basics eg loop through visible only fields/items, disabling screenupdating/calculations however there is one thing which I haven't been able to figure out which would help immensely. When the code shows all details I've created loops which go through each visible field and then each visible item and puts the showdetail property to True. My thought was that I should be able to just apply the showdetail property to the visible fields and avoid looping through all of the items? You can do this in the spreadsheet by selecting the pivotfield cell and clicking the show detail button or menu item. So I recorded the macro and got: Selection.ShowDetail = True (The selection being the cell which has the field name, Target is the pivot table object as provided by the update event) Unfortunately I haven't been able to get it to work by referencing the pivot table object. This is the code I have: For Each pf In Target.VisibleFields For Each pi In pf.PivotItems pi.ShowDetail = True Next pi Next pf This is what I'd like to work but it doesn't For Each pf In Target.VisibleFields pf.ShowDetail = True Next pf and I've tried this too? For Each pf In Target.VisibleFields pf.labelRange.ShowDetail Next pf Any thoughts? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Embedded Chart Versus Chart - ShowDetail Issue | Charts and Charting in Excel | |||
PivotItem.Visible | Excel Programming | |||
PivotItem positioning... | Excel Programming | |||
VBA / Pivottable / showdetail - problem | Excel Programming | |||
How to add a PivotItem ? | Excel Programming |