Pivot Table Issue...

  • Thread starter Trevor Williams
  • Start date
T

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
 
T

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
 
D

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top