How to eliminate any new pivot Items

[Randy]

Hi all,

I have a pivot table that I dynamically generate off of a
named field source, showing roles, names and hours per
week.
The trick is this - I have a pivot table that only shows
1 role ("painter"). But, when my users update the source
data, they may add a new role to it, that's not marked as
pivottable.items.role("builder").visible= false.

so, if the new data introduces "Janitor" to my role
field, my code won't have it marked as visible=false,
because it wasn't there.

Is there a way to code a loop based on pivot items.
Something like:
"Do While pivotitems.role<>"Builder"
pivotitems.visible=false"

Right now i have about 120 different role.visible=false
statements.

Any help or code snippets would be greatly appreciated.

 

[Debra Dalgleish]

You could use code similar to the following:

Sub HidePivotItems()
'hide all pivot items in all tables on sheet
'except specified item
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem

On Error Resume Next
For Each pt In ActiveSheet.PivotTables
Set pf = pt.PivotFields("Role")
pf.AutoSort xlManual, "Role"
For Each pi In pf.PivotItems
If pi.Value = "Painter" Then
pi.Visible = True
Else
pi.Visible = False
End If
Next
pf.AutoSort xlAscending, "Role"
Next
End Sub

 

[Randy]

Fantastic!! I knew there was a way to do it, just
couldn't construct the syntax. thanks a ton!!

 

[Debra Dalgleish]

You're welcome!

Fantastic!! I knew there was a way to do it, just
couldn't construct the syntax. thanks a ton!!


of a