Macro for Pivot in MS Excel 2003 vs 2007

  • Thread starter Thread starter MichaelR
  • Start date Start date
M

MichaelR

Hi,

I have the following macro that clears all filters in a pivot table of mine
in 2007:

Sheets(2).PivotTables("PivotTable1").ClearAllFilters.

When I try to run the macro in 2003 I get an error message that says:
"Object doesn't support the property or method."

Any ideas for how I can clear all filters in my pivot table in excel 2003
using a macro.

Thanks.
 
'=========================
Sub PivotShowItemResetSort()
'show all items in field
'sort is set to Manual to prevent errors, e.g.
'unable to set Visible Property of PivotItem class
'returns sort order to previous setting
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Dim intASO As Integer

Application.ScreenUpdating = False
Application.DisplayAlerts = False
On Error Resume Next
For Each pt In ActiveSheet.PivotTables
pt.ManualUpdate = True
For Each pf In pt.VisibleFields
intASO = pf.AutoSortOrder
pf.AutoSort xlManual, pf.SourceName
For Each pi In pf.PivotItems
pi.Visible = True
Next pi
pf.AutoSort intASO, pf.SourceName
pf.CurrentPage = "(All)"
Next pf
pt.ManualUpdate = False
Next pt
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
'=========================
 
Back
Top