Pivot Items Visible

L

LuisE

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
 
R

Roger Govier

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top