Hide all PivotItems except for a specific Item
 

I really hope someone can help me with this. I am trying to make visible a
specified Pivot Item within a specific PivotField within a specific Pivot
Table. I have been trying to adapt some code to hide all Pivot Items for all
RowFields for all PivotTables but I haven't been very successful at it. Any
help I could get would be greatly appreciated.

Sub Shell()
Run SpecPivotItemsVisible("PivotTable6", "Country", "US")
End Sub

Sub SpecPivotItemsVisible(PivotTbl As String, PivField As String, PivItem As
String)
'hide all pivot items in all tables on sheet
'except last item
Dim pt As PivotTable 'Pivot table
Dim pf As PivotField 'Pivot field
Dim pi As PivotItem ' specific pivot item
Dim opi As PivotItem 'other pivot items


pt = Worksheets("Hidden).PivotTables(PivotTbl).value
pf = pt.PivotFields(PivField)
pi = pf.PivotItems(PivItem)

On Error Resume Next

With Worksheets("Hidden").PivotTables(pf)
RowField("gee").AutoSort xlManual
For Each opi = not pi In PivotField("pf").PivotItems
pi.Visible = False
Next
RowField("pf").AutoSort xlManual
End With

End Sub

Hide all PivotItems except for a specific Item
 

The following code should work:

'=================================
Sub Shell()
SpecPivotItemsVisible "PivotTable6", "Country", "US"
End Sub
'==============================
Sub SpecPivotItemsVisible(PivotTbl As String, PivField As String,
PivItem As String)
'hide all pivot items in all tables on sheet
'except last item
Dim pt As PivotTable 'Pivot table
Dim pf As PivotField 'Pivot field
Dim pi As PivotItem ' specific pivot item

For Each pt In Worksheets("Hidden").PivotTables
Debug.Print pt.Name
Next pt

Set pt = Worksheets("Hidden").PivotTables(PivotTbl)

Set pf = pt.PivotFields(PivField)

On Error Resume Next

pt.ManualUpdate = True

With pf
.AutoSort xlManual, .SourceName
.PivotItems(PivItem).Visible = True
For Each pi In .PivotItems
If pi.Name < PivItem Then
pi.Visible = False
End If
Next
.AutoSort xlAscending, .SourceName
End With

pt.ManualUpdate = False

End Sub

'=====================================

DynamiteSkippy wrote:
I really hope someone can help me with this. I am trying to make visible a
specified Pivot Item within a specific PivotField within a specific Pivot
Table. I have been trying to adapt some code to hide all Pivot Items for all
RowFields for all PivotTables but I haven't been very successful at it. Any
help I could get would be greatly appreciated.

Sub Shell()
Run SpecPivotItemsVisible("PivotTable6", "Country", "US")
End Sub

Sub SpecPivotItemsVisible(PivotTbl As String, PivField As String, PivItem As
String)
'hide all pivot items in all tables on sheet
'except last item
Dim pt As PivotTable 'Pivot table
Dim pf As PivotField 'Pivot field
Dim pi As PivotItem ' specific pivot item
Dim opi As PivotItem 'other pivot items


pt = Worksheets("Hidden).PivotTables(PivotTbl).value
pf = pt.PivotFields(PivField)
pi = pf.PivotItems(PivItem)

On Error Resume Next

With Worksheets("Hidden").PivotTables(pf)
RowField("gee").AutoSort xlManual
For Each opi = not pi In PivotField("pf").PivotItems
pi.Visible = False
Next
RowField("pf").AutoSort xlManual
End With

End Sub



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