Pivot Table Pivot Item Visible - Why so difficult

[headly]

A recorded macro does this code

With ActiveSheet.PivotTables("PivotTable1").PivotFields("Product Name")
.PivotItems("Aniseed Syrup").Visible = True
End With

Yet when i try the comand in the immediate window i get run time error 1004
unable to set the visible property of the pivotitem class

Am i missing a reference? As i type pivot code, i don't get any code hints.
is this a late binding problem?

 

[Debra Dalgleish]

Add code to set the sort to manual, and that should prevent the error:

With ActiveSheet.PivotTables("PivotTable1").PivotFields("Product Name")
.AutoSort xlManual, .SourceName
.PivotItems("Aniseed Syrup").Visible = True
.AutoSort xlAscending, .SourceName
End With

 

[Joe]

Add code to set the sort to manual, and that should prevent the error:

With ActiveSheet.PivotTables("PivotTable1").PivotFields("Product Name")
     .AutoSort xlManual, .SourceName
        .PivotItems("Aniseed Syrup").Visible = True
     .AutoSort xlAscending, .SourceName
End With

Just a followup Qn on Pivot Table.

I want to refresh the Pivot table automaticall and I use the following
code.
D7 is the first cell in that Table.

Range("D7").Select
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh

It works fine most of the time, but show some error sometimes.
Any idea why? or Is there any better way of achieving the same?

Thanks a lot
Joe

 

[headly]

That's great but why did it work? I'm writing a course on VBA and my students
will likely ask what is the sort command and why is it necessary? TIA

 

[Debra Dalgleish]

You don't need to select a cell in the pivot table, so you could delete
that line of code.
What error message do you get?

 

[Debra Dalgleish]

The sort setting is the one you can manually change in the Field
Settings, when you click the Advanced button.
If it's manually or programmatically set to Manual, you shouldn't get
the error when making pivot items visible.
If it's set to Ascending or Descending, you'll get the error when you
programmatically try to make an item visible.
I don't know the reason why.

 

[headly]

Thank you so much; I hope you don't mind if I give you credit in my
courseware! I will also highly recommend your books on pivot stuff, you are
far away too expert.
Happy holidays, you rock!

 

[Dave Peterson]

You may want to share Debra's site with your students, too.

 

[Debra Dalgleish]

You're welcome! Thanks for letting me know that it helped.

 

[gorki]

Hi all,

I get the same error, even when I set the autosort to manual... Could you please help me?



Sample of the code:

With pt1.PivotFields("CREATE_DATE")
.AutoSort xlManual, "CREATE_DATE"
'.PivotItems(5).Visible = True 'Tried this does not work...
.PivotItems("14/12/2011").Visible = True 'this expression is equal to the one recorded _ by the macro recorder and it still does not work

End With


When, in the autosort options, instead of "CREATE_DATE" I write .SourceName, I still get the same error message...

I'm borderline desperate. =D

 

[Anon_F]

Gorki the thread is nearly 4 years old!
Starting a new one may help you more.

 

[gorki]

Ok Thanks!