Pivot Table VBA Question

Hello,

I'm trying to write VBA code to hide all fields of a pivot table except one. I don't know what all the fields will be, except for the one I want to remail visible. Here's what I have so far:

Sub Macro1()
Dim Region As String
Region = Range("E16").Value
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Label")
.PivotItems("*").Visible = False
.PivotItems(Region).Visible = True
End With
End Sub

Unfortunately, the code errors on the "*" wildcard. Any help on a workaround would be much appreciated! Thanks.

Brad

Hi Brad,

Take a look at the code Debra wrote ...
http://www.contextures.com/xlPivot03.html

You're the MAN, Carim! Here's the code, if anyone else is interested...

Sub Macro1()
Dim Region As String
Dim PI As PivotItem
On Error Resume Next
'Hide all pivot table items.
For Each PI In ActiveSheet.PivotTables("PivotTable1").PivotFields("Label").PivotItems
PI.Visible = False
Next PI
'Show the pivot table item you want.
Region = Range("E16").Value
ActiveSheet.PivotTables("PivotTable1").PivotFields("Label").PivotItems(Region).Visible = True
End Sub

Glad you fixed your problem ...

Thanks to Debra ...