M
MatNorth
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?
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?