how to set pivotitem.visible property to true

[Amedee Van Gasse]

I use the following code:

Public Sub ResetFilters()

Dim pvtTable As PivotTable
Dim pvtField As PivotField
Dim pvtItem As PivotItem

Set pvtTable = ActiveSheet.PivotTables("afsct")

For Each pvtField In pvtTable.PivotFields
pvtField.AutoSort xlManual, pvtField.SourceName
For Each pvtItem In pvtField.PivotItems
If Not pvtItem.Visible Then pvtItem.Visible = True
Next
pvtField.AutoSort xlAscending, pvtField.SourceName
Next

End Sub


This gives an error 1004 on the line with pvtField.AutoSort:
Door de toepassing of door object gedefinieerde fout
(by the application or by object defined error)


When I comment the two pvtField.AutoSort lines, I get an error 1004:
Eigenschap Visible van klasse PivotItem kan niet worden ingesteld.
(property Visible of class PivotItem cannot be set)

What am I missing here?

 

[Amedee Van Gasse]

I forgot: Excel 2003.

 

[Amedee Van Gasse]

I use the following code:

Public Sub ResetFilters()

Dim pvtTable As PivotTable
Dim pvtField As PivotField
Dim pvtItem As PivotItem

Set pvtTable = ActiveSheet.PivotTables("afsct")

For Each pvtField In pvtTable.PivotFields
pvtField.AutoSort xlManual, pvtField.SourceName
For Each pvtItem In pvtField.PivotItems
If Not pvtItem.Visible Then pvtItem.Visible = True
Next
pvtField.AutoSort xlAscending, pvtField.SourceName
Next

End Sub

This gives an error 1004 on the line with pvtField.AutoSort:
Door de toepassing of door object gedefinieerde fout
(by the application or by object defined error)

When I comment the two pvtField.AutoSort lines, I get an error 1004:
Eigenschap Visible van klasse PivotItem kan niet worden ingesteld.
(property Visible of class PivotItem cannot be set)

What am I missing here?

If this is totally the wrong approach, please tell me.
Perhaps I should throw away the pivot table and recreate it from
scratch, from code?
Would that work?
I fear it would be slow, some of our users have HUGE tables...

 

[Amedee Van Gasse]

I use the following code:

Public Sub ResetFilters()

Dim pvtTable As PivotTable
Dim pvtField As PivotField
Dim pvtItem As PivotItem

Set pvtTable = ActiveSheet.PivotTables("afsct")

For Each pvtField In pvtTable.PivotFields
pvtField.AutoSort xlManual, pvtField.SourceName
For Each pvtItem In pvtField.PivotItems
If Not pvtItem.Visible Then pvtItem.Visible = True
Next
pvtField.AutoSort xlAscending, pvtField.SourceName
Next

End Sub

This gives an error 1004 on the line with pvtField.AutoSort:
Door de toepassing of door object gedefinieerde fout
(by the application or by object defined error)

When I comment the two pvtField.AutoSort lines, I get an error 1004:
Eigenschap Visible van klasse PivotItem kan niet worden ingesteld.
(property Visible of class PivotItem cannot be set)

What am I missing here?

This one is just for the archive.

TWO problems!

1. Despite what the Excel help says, DO NOT use pvtField.SourceName.
It will not work!
Solution: Use pvtField.Caption

2. If the pivot table has "ghost"-items, the code will break when you
try to set it visible.
Solution: first delete all pivot items. This cleans up all cached
ghosts, but doesn't delete the real items.
Only then you can set all remaining items visible.
Disadvantage: the first time you clean out the ghosts, it will take a
long time. In one test file, up to a minute.
After that, 10-15 seconds.
Consider creating two separate subs:
* one for cleaning the gosts
* one for setting the remaing items visible.

 

[Dave Peterson]

I ignore any error caused by those "ghost" items:

For Each pvtItem In pvtField.PivotItems
If Not pvtItem.Visible Then pvtItem.Visible = True
Next

becomes

on error resume next
For Each pvtItem In pvtField.PivotItems
If Not pvtItem.Visible Then pvtItem.Visible = True
Next pvtItem
on error goto 0

I don't feel as bad about it as I did when I was younger <vbg>.

 

[Amedee Van Gasse]

I ignore any error caused by those "ghost" items:
*snip*

    on error resume next
*snip*

I don't feel as bad about it as I did when I was younger <vbg>.

Point taken. ;-)

Anyway, I feel good because I found a way to clean up pivot tables.
Clean pivot table = faster calculation and smaller file.
Smaller files = network admin happy.
I like making friends, you never know when I might need a small
favor