Hide all except one in a Pivot Field

shutter

New Member
Joined
Oct 29, 2006
Messages
4
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
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,823
Hi Greg
Welcome to the board

You can loop through all the PTItems.

Code:
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

Well-known Member
Joined
Apr 28, 2004
Messages
75,817
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Oct 29, 2006
Messages
4
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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,711
Messages
5,524,436
Members
409,577
Latest member
Dwg

This Week's Hot Topics

Top