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

 

[SkipVought]

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

1. Is the sheet containing the pivot table ACTIVE when you run? Better to explicitly reference the sheet object...

    [b]Sheets("YourSheetName")[/b].PivotTables("PivotTable2").PivotFields("Region").PivotFilters.Add _
        Type:=xlCaptionBeginsWith, Value1:="ceema"

2. Is the PivotTable actually named PivotTable2?

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

 

[HairyHippy]

1. Yes it is.
2. Yes it is.

By adding the sheet("name") in front makes no difference and I also tried changing the pivot name to see if that would resolve. I also re-recorded and started on a different sheet to ensure correct sheet selected.

Sub Macro6()
'
' Macro6 Macro
'

'
    Sheets("New New Deals").Select
    Sheets("New New Deals").ActiveSheet.PivotTables("NewNewPivot").PivotFields("Region").PivotFilters.Add _
        Type:=xlCaptionBeginsWith, Value1:="ceema"
End Sub

 

[SkipVought]

...and the result of recording a new macro was???

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

 

[HairyHippy]

I posted the code in the above post. It still doesn't work, get same run time error.

 

[SkipVought]

Sub Macro6()
'
' Macro6 Macro
'

'
    Sheets("New New Deals").Select
    Sheets("New New Deals")[s].ActiveSheet[/s].PivotTables("NewNewPivot").PivotFields("Region").PivotFilters.Add _
        Type:=xlCaptionBeginsWith, Value1:="ceema"
End Sub

Remove .ActiveSheet

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

 

[HairyHippy]

Nope, 'fraid that didn't work either.

Essentially what I'm trying to do is quicken up the macro by using a label filter and selecting that filter by code rather than the following code, which only loops and thereofre takes forever to run:

With ActiveSheet.PivotTables("PivotTable2").PivotFields("Sales Team")
   .PivotItems("Value 1").Visible = True
   .PivotItems("Value 2").Visible = False
   .PivotItems("Value 3").Visible = False
   .PivotItems("Value 4").Visible = False
   .PivotItems("Value 5").Visible = False
   .PivotItems("Value 6").Visible = False
    etc
    etc
    etc
End With

 

[SkipVought]

Nope, 'fraid that didn't work either.

Well, my friend, your code WORKS in my workbook!

So you must have some object (sheet, pivot table, pivot field) that is not correctly named in accordance with your posted code, because I created a sheet named New New Deals and a PivotTable named NewNewPivot with a PivotField named Region, containing in the Source Data...
[tt]
Region
ceve
ceema
other
ceema more
[/tt]
which, when filtered by your code, displays...
[tt]
Count of Region
Region Total
ceema 1
ceema more 1
Grand Total 2
[/tt]
So if you cannot supply relevant information regarding this issue, you cannot be helped!

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

 

[HairyHippy]

Wooh hang on a minute Skip, don't forget this was a recorded macro and I thought, but maybe I'm wrong, would put in what it found in the sheet e.g. all the naming conventions. I didn't touch what excel had recorded.

I've supplied all I can regarding the issue. I will also try to recreate in another workbook like you have and see if it works.

It does seem very strange to me that a recorded macro won't work though...

Thanks for recreating and proving that this should actually work though.

 

[SkipVought]

Please post your PivotTable HEADINGS and a few sample rows of data.

What version of Excel?

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

 

[HairyHippy]

I'm afraid I can't the data is confidential. I wish I could, it obviously make this a lot easier.

What I did find interesting is that the macro I recorded has "Region" in it, yet the last set of code I posted above has "Sales Team". I changed the Region to Sales Team to see if this would solve the issue but unfortunately it didn't.

The last code above that I posted works perfectly well, just makes the macro slow, hence trying to use the label filter method instead.

I'm using Excel 2007.

 

[SkipVought]

I NEED to see the headings: COPY 'n' PASTE.

Forget the data.

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

 

[HairyHippy]

Heading 1 Heading 2 Heading 3 Heading 4
Sales Team Deal Stage Rep Name Total ($M)


Region is the orginal source name, Sales Team is custom name. I wonder if this is what it is not liking?

 

[SkipVought]

Do ya think!

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

 

[HairyHippy]

But neither sales team or region works as stated above. Are we saying that you can't change the name using the field settings options or a macro won't work?

I'll change it back to region in the field settings form box and test... But the thing is that in the report I need it to say Sales Team Not region and therefore would have to tell the macro to renaming back to region, do the label fiter and change it back to sales team.

 

[HairyHippy]

Ok that's precisely this issue.

Basically the base data used to create the pivot has a column that called Region (this can't be changed as it's in the source system. Pivot then created and the heading in the pivot changed to Sales Team using the field settings option, so source still says region but custom says sales team. Record macro to filter on "sales team" and output says region. Run macro and it won't. Look at macro code and change region to sales team, still doesn't work. Conclusion: vb doesn't like one changing the heading name using the field settings option.

 

[SkipVought]

Are you ABSOLUTELY CERTAIN that your PivotTable name is NewNewPivot???

msgbox activesheet.pivottables(1).name

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

 

[HairyHippy]

Skip thanks but the issue is due to using custom names rather than original source names, I've proved this out both in my orginal and recreating along the same lines as you did. You can test this if you like in the one you created. Go to field settings and change the name from something other than region.

This seems to be a flaw with VB/Excel...

 

[SkipVought]

I have changed the name and the code works for the changed name

    Sheets("New New Deals").PivotTables("NewNewPivot").PivotFields("[b]Other Name[/b]").PivotFilters.Add _
        Type:=xlCaptionBeginsWith, Value1:="ceema"

[tt]
Count of Region
Other Name Total
ceema 1
ceemasss 1
Grand Total 2
[/tt]


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

 

[HairyHippy]

Skip thanks for your help with this, much appreciated. I can't work out why it works for you and not for me so I'm going to stick to my orignal code for now, which was the "with" statement one above. What is odd is in that one it uses Sales Team and works perfectly fine but won't for this label filter one and that's why I can't get my head around why this isn't working for me.