Pivot Table Field Choices

  • Thread starter Thread starter Sue
  • Start date Start date
S

Sue

Is there a global way to uncheck ALL of the boxes in a
pivot table field button pull down? I see the way to do
this if you have 2 levels but all of mine are single
level, and I have a hundred plus. Thanks in advance if
anyone has a trick!
 
You can do this with a macro. The first macro hides all items except the
last one, and the second one shows all items. Replace "Rep" with the
name of your field.

Sub PivotHideItemsField()
'For version 2000 -- hide all items in specific field
'sort is set to Manual to prevent errors, e.g.
'unable to set Visible Property of PivotItem class
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Set pt = ActiveSheet.PivotTables(1)
Set pf = pt.PivotFields("Rep")
Application.ScreenUpdating = False
Application.DisplayAlerts = False
On Error Resume Next
With pt.PivotFields("Rep")
pf.AutoSort xlManual, pf.SourceName
For Each pi In pf.PivotItems
pi.Visible = False
Next pi
pf.AutoSort xlAscending, pf.SourceName
End With
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

Sub PivotShowItemsField()
'For version 2000 -- show all items in specific field
'sort is set to Manual to prevent errors, e.g.
'unable to set Visible Property of PivotItem class
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Set pt = ActiveSheet.PivotTables(1)
Set pf = pt.PivotFields("Rep")
Application.ScreenUpdating = False
Application.DisplayAlerts = False
On Error Resume Next
With pt.PivotFields("Rep")
pf.AutoSort xlManual, pf.SourceName
For Each pi In pf.PivotItems
If pi.Visible <> True Then
pi.Visible = True
End If
Next pi
pf.AutoSort xlAscending, pf.SourceName
End With
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
 
Back
Top