Excel 2002 only pivot table feature?

  • Thread starter Thread starter Keith
  • Start date Start date
K

Keith

Hi All,

I have a pivot table that was created in Excel 2002. My
co-workers who view the same pivot table in Excel 2000
don't get a "Show All" selection to "select
all"/"deselect all" row field data when they click on the
row field item down-arrow. I hope someone can help me
with this question...Is the "Show All" selection an Excel
2002 only feature or is there a setting/feature in Excel
2000 that can produce the same result as the Excel
2002 "Show All" selection?

Keith
 
Excel 2000 doesn't have this feature, but you can show all or hide all
items by using 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