Pivot table selection

[adyan76]

Hi Experts,

I am trying to unselect the pivot items and then select just one item with vba which equals one of my variable?

Could you give me some advises regarding this issue. I appreciate your responses.

i am using the following code which is not working

Sub test() 
 
 
Dim n As Integer 
Dim x As Integer 
 
n = ActiveSheet.PivotTables("PivotTable5").PivotFields("xx").PivotItems.Count 
 
For x = 1 To n 
 
On Error Resume Next 
With thePT.PivotFields("xx") 
 
If x = myvariable Then 
.PivotItems(x).Visible = True 
Else 
.PivotItems(x).Visible = False 
 
End If 
DoEvents 
End With 
Next x 
End Sub  

[Andy Pope]

try this,

Please Login or Register  to view this content.

If that fails then post information about the error raised.
Better yet post example file.

[adyan76]

Thanks Andy, the code works perfectly.

[adyan76]

I am now able to apply the filter on a rowfield but when my variable changes then this code does not work. I have filtered the rowfield "name" by value "a". If I afterward want to filter this field by name "b", then I dont get the desired result. Any idea what is going on there?
Here is the code

I am attaching the file as well

Sub test() 
 
 
Dim n As Integer 
Dim x As Integer 
 
Dim myvariable 
Dim thePT As PivotTable 
 
Set thePT = ActiveSheet.PivotTables("PivotTable4") 
n = ActiveSheet.PivotTables("PivotTable4").PivotFields("name").PivotItems.Count 
 
myvariable = "a" 
 
For x = 1 To n 
 
With thePT.PivotFields("name") 
 
If .PivotItems(x).Name = myvariable Then 
.PivotItems(x).Visible = True 
Else 
.PivotItems(x).Visible = False 
 
End If 
 
End With 
Next x 
End Sub  

[Andy Pope]

You can not make all the items invisible.

So first go through a loop that makes all items visible.

Please Login or Register  to view this content.