Pivot Table Field Choices

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!
 
D

Debra Dalgleish

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top