Prev: Making A Particular Wooksheet Active
Next: I have something similar
From: LuisE on 23 Mar 2010 20:06
I'm trying to make all pivot items non visible and then mak only one visible
based on a cell value.


Dim pitem As PivotItem



Sheet3.PivotTables("TypesOfService").ManualUpdate = True

''''''Show all items
For Each pitem In
Sheet3.PivotTables("TypesOfService").PivotFields("Type of service").PivotItems
pitem.Visible = False
Sheet3.PivotTables("TypesOfService").PivotFields("Type of
service").PivotItems("(blank)").Visible = False
Next


Thanks in advance
From: Roger Govier on 24 Mar 2010 06:33
Hi Luis

Here is part of some code I use, to only show pivot items that lie
between two dates. In my case, the Date field is a page Item

I set them all to Visible first, then hide the invalid ones, but it
would work equally well the other way around.

Sub FilterPivotDatesFeed()

Dim dStart As Date, dEnd As Date, wks As Long
Dim pt As PivotTable, pf As PivotField, pi As PivotItem

On Error Resume Next

Set pt = ActiveSheet.PivotTables(1)
Set pf = pt.PivotFields("Date")

pt.ManualUpdate = True

pf.EnableMultiplePageItems = True

For Each pi In pf.PivotItems
pi.Visible = True
Next pi

For Each pi In pf.PivotItems
If pi.Value < dStart Or pi.Value > dEnd Then
pi.Visible = False
End If
Next pi

pt.ManualUpdate = False

Hope this helps you.

--
Regards
Roger Govier

LuisE wrote:
> I'm trying to make all pivot items non visible and then mak only one visible
> based on a cell value.
>
>
> Dim pitem As PivotItem
>
>
>
> Sheet3.PivotTables("TypesOfService").ManualUpdate = True
>
> ''''''Show all items
> For Each pitem In
> Sheet3.PivotTables("TypesOfService").PivotFields("Type of service").PivotItems
> pitem.Visible = False
> Sheet3.PivotTables("TypesOfService").PivotFields("Type of
> service").PivotItems("(blank)").Visible = False
> Next
>
>
> Thanks in advance
 | 
Pages: 1
Prev: Making A Particular Wooksheet Active
Next: I have something similar