Pivot Table Pivot Item Visible - Why so difficult

  • Thread starter Thread starter headly
  • Start date Start date
H

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?
 
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
 
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
Click to expand...



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
 
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
 
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?
 
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.
 
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!
 
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
 
Back
Top