Code to Show All Pivot Table Items

  • Thread starter Thread starter Chris
  • Start date Start date
C

Chris

I have a pivot table and a user form that allows the user
rearrange the pivot table by just clicking a button. The
problem I'm having is when they hide items from a field
then rearrange the table, those items remain hidden. Is
there a code I can write that checks the "(Show All)"
option on a field before my other code runs to alter the
pivot table?
 
The following code will show all items for all visible fields:

'==============================
Sub PivotShowItemAllVisible()
'For version 2000 -- show all items in 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
Application.ScreenUpdating = False
Application.DisplayAlerts = False
On Error Resume Next
For Each pt In ActiveSheet.PivotTables
For Each pf In pt.VisibleFields
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
Next pf
Next pt
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
'=====================================
 
Back
Top