Pivot Table Issue...

[Trevor Williams]

Dear All

Can anyone shed any light on why my pivot table won't show the PivotItems
using the following code?

Sub TestPiv()
Dim myPiv As PivotTable, myPI As PivotItem
Set myPiv = ActiveSheet.PivotTables("PivotTable1")
For Each myPI In myPiv.PivotFields("Year to View").PivotItems
'On Error Resume Next
myPI.Visible = True
Next
End Sub

If I record a macro to 'show all' I get the following code which, when run
again, doesn't work either. Very odd.

Sub Macro4()'
' Macro4 Macro
' Macro recorded 06/05/2008 by .
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Year to View")
.PivotItems("2007").Visible = True
.PivotItems("2008").Visible = True
.PivotItems("2010").Visible = True
.PivotItems("2011").Visible = True
.PivotItems("2012").Visible = True
.PivotItems("2013").Visible = True
.PivotItems("2014").Visible = True
End With
End Sub

Any help appreciated

Trevor Williams

 

[Trevor Williams]

Forgot to mention I get the following error message:

Run Time Error '1004'
Unable to set the Visible property of the PivotItem class

 

[Debra Dalgleish]

If the field is set for automatic sort, you'll get an error. You can add
code to set the sort to manual, e.g.:

With myPiv.PivotFields("Year to View")
.AutoSort xlManual, .Name
End With

For Each myPI In myPiv.PivotFields("Year to View").PivotItems
'On Error Resume Next
myPI.Visible = True
Next