Fail to filter pivot table item

[perco754]

Hi,


Using Excel 2003

This code tries to filter a pivot table using VBA:

I get an error "Unable to set the visible property of the pivotiten class" when trying to set pivItem.Visible = true
Why?

I declare pivItem as PivotItem and machine is a string

                For Each pivItem In pivt.PivotFields(machine).PivotItems
                    pivt.ManualUpdate = True
                    pivt.PivotFields(machine).AutoSort xlManual, machine
                    If pivItem.Visible = False Then
                        pivItem.Visible = True
                    End If
                    pivt.ManualUpdate = False
                Next

 

[Fazza]

Maybe the item isn't in the dataset? You should be able to check via debugging.

Also, consider adding a line like "pvt.pivotcache.missingitemslimit = xlmissingitemsnone" followed by a refresh ("pvt.pivotcache.refresh") before your present code

HTH

 

[perco754]

Thanks Fazza, it worked straight away

But why? Because of the refresh and/or the missingitemslimit?

Anyhow, a big thank you!

 

[Fazza]

Certainly in Excel 2003 (and for all I know, the same in all versions) if you make a pivot table and your 'machine' field has entries like "one", "two", "three" then later delete all "two" entries from the source data it remains in the pivot table field's list of items. In such a case, your code comes along and tries to set as visible the item for "two" but it isn't in the dataset anymore. There will be many old forum posts where people ask how to remove these old 'ghost' values. A way to remove these obsolete entries is remove the field from the pivot table, refresh it, and then return the field. Or programmatically with the two lines I gave.

 

[perco754]

Ok, thanks again for the explanation. It's always good to know why certain things suddenly start to work

Another thing with my pivot tables. If I manually filter them there are cases where the data is not showing up in the PT inspite I can see the data in the source workbook after performing the same filtering visually.
I've tried doing manual resets but without effect.
I've also resetted the PT source reference path through the PT Wizard.

BR

 

[perco754]

Ignore my last question, there were some page fields that I forgot to set, sorry

BR