Recorded macro not running 1

[HairyHippy]

I recorded the following macro:

Sub Macro5()
'
' Macro5 Macro
'

'
    ActiveSheet.PivotTables("PivotTable2").PivotFields("Region").PivotFilters.Add _
        Type:=xlCaptionBeginsWith, Value1:="ceema"
End Sub

Yet when it runs it returns a run time error 1004: "Unable to get the pivotfields property of the pivottable class"

Any idea on how to resolve?

TIA

 

[HairyHippy]

Another thought as to why it might or might not be working is checking or unchecking "Classic PivotTable layout"....

 

[SkipVought]

I used the Classic layout.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[HairyHippy]

Ok getting odder....

New file, all else closed.

Sheet 1:
Region Bal
ABC 100
DEF 50
GHI 8222
ABC 3654
DEF 546984
GHI 8548

Create Pivot in new worksheet - becomes Sheet 4. Put Region in Row Labels and Bal in Values (Sum of Bal).

Change field setting of Region to Sales Team.

Pivot Table Options, set pivot table name to "Test" and switch to Classic PivotTable Layout

Record Macro to label fiter beginning with "a":

Sub Macro1()
'
' Macro1 Macro
'

'
    ActiveSheet.PivotTables("Test").PivotFields("Region").PivotFilters.Add Type:= _
        xlCaptionBeginsWith, Value1:="a"
End Sub

Fails as you'd expect, so change to

Sub Macro1()
'
' Macro1 Macro
'

'
    ActiveSheet.PivotTables("Test").PivotFields("Sales Team").PivotFilters.Add Type:= _
        xlCaptionBeginsWith, Value1:="a"
End Sub

Also fails. Manually clear filter and run macro. WORKS PERFECTLY. Run Macro again and it FAILS! Manually clear filter and run macro again, it WORKS!

Change code to

Sub Macro1()
'
' Macro1 Macro
'

'
    ActiveSheet.PivotTables("Test").PivotFields("Sales Team").ClearAllFilters
    ActiveSheet.PivotTables("Test").PivotFields("Sales Team").PivotFilters.Add Type:= _
        xlCaptionBeginsWith, Value1:="a"
End Sub

and run. Now works perfectly...

.... so found the solution but why does the filter need to be cleared before reapplying?

 

[SkipVought]

This worked on the PT...
[tt]
Row Labels Sum of Bal
ABC 3754
DEF 547034
GHI 16770
Grand Total 567558
[/tt]

Sub Macro1()
'
' Macro1 Macro
'

'
    ActiveSheet.PivotTables(1).PivotFields("Region").PivotFilters.Add Type:= _
        xlCaptionBeginsWith, Value1:="a"
End Sub

[tt]
Row Labels Sum of Bal
ABC 3754
Grand Total 3754
[/tt]


So glad you discovered a solution. Your persistence paid off. Keep pressing on!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]