Hide all except one in a Pivot Field

[shutter]

Hi Everyone,

I have some pivot tables that contain 10's of thousands of data lines, and each line has a rep name in a PivotField "Rep". Rep names change dynamically and there are approximately 100 reps.
I want the ability to turn visibility off, of all reps in the pivot field, except when the rep field contains a string value of "repname".

This is to selectively copy data out to individual files that do not contain the full pivot source.

In effect, I want to accomplish the below by saying "make all reps not visble, except if name = Harry"

With ActiveSheet.PivotTables("PivotTable2").PivotFields("Rep")
.PivotItems("John").Visible = False
.PivotItems("Harry").Visible = True
.PivotItems("Fred").Visible = False
end with

I hope that makes sense!
Greg

 

[pgc01]

Hi Greg
Welcome to the board

You can loop through all the PTItems.

Sub SetPTIVis()
Dim PTI As PivotItem, sPTI As String

sPTI = "Mary"
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Rep")
    .PivotItems(sPTI).Visible = True
    For Each PTI In .PivotItems
        If PTI.Value <> sPTI Then PTI.Visible = False
    Next
End With
End Sub

Remark: If you have excel 2003 you may be able to simplify the code because there's a new option "Show all" in the PivotFields.

Hope this helps
PGC

 

[Norie]

shutter

If you just want to split out the pivot table by Rep then set Rep as a page item.

You can then just right click and select Show Pages...

 

[shutter]

Thank you for your help! I shall try that.

As for setting the rep field as a page item, yes, that would make it easier, but it needs to be a column field as the table is used as the source of other data where I require it to be a column field

Thank you,
Greg