How to fix this problem? "unable to set the visible property of the pivotitem class"

[IvyZhou]

Hi Guys,

I am trying to manipulate an Pivot Table trough VBA so I can loop trough the categories of the pivot table, set all to invisible but one. However, it does not work and vba said "unable to set the visible property of the pivotitem class"

The past of my code is:

Set pvtFld = ActiveSheet.PivotTables(PivotTable_Name(k)).PivotFields(ListFields(1))


For m = 1 To PvtTbl.PivotFields(ListFields(1)).PivotItems.Count
For Each pvtItm In PvtTbl.PivotFields(ListFields(1)).PivotItems
If UCase(pvtItm.Value) = PvtTbl.PivotFields(ListFields(1)).PivotItems(m) Then
pvtItm.Visible = True
Else
pvtItm.Visible = False
End If
Next pvtItm
PvtTbl.PivotCache.MissingItemsLimit = xlMissingItemsNone
Next m

Next k


Can anyone tell me how to fix this problem?





I have another way to write this code, also it does not work. Can anyone also help me to look at it?

Here is my code:


For Each pvtItm In PvtTbl.PivotFields(ListFields(1)).PivotItems
For Each pi2 In PvtTbl.PivotFields(ListFields(1)).PivotItems
If pi2 <> pvtItm Then
pi2.Visible = False
End If
Next pi2

Next pvtItm

[Paul_Hossler]

Please use the [#] icon to add [ CODE ] tags and pase code between them -- makes it easier to read and to see

If you record as macro that hides just one, what does that look like?

Can you post a small sample workbook that shows the issue?