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.

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 wrote:
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
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

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

-----Original Message-----
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 wrote:
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
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

.

You're welcome!

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


-----Original Message-----
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 wrote:

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
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

.




--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html