Pivot Table VBA Filtering

[sulley333]

Hi!

I am looking to only show two columns in my pivot table based on what the user selects out of a combo box. When recording it I only get the following:

With ActiveSheet.PivotTables("PivotTable1").PivotFields("Brand")
.PivotItems("Just For You").Visible = False
.PivotItems("(blank)").Visible = False
End With

This doesn't help me because I won't always know which variables to hide. Is there a way to get it to only select certain ones. I tried the reverse = True but that didn't work. I thought maybe I would hide them all and then show the ones I want but it won't let me hide them all. Any ideas??

Thanks,
Lauren

 

[RoryA]

Make the first one you do want shown visible, then loop through all the items and set Visible = False if the names are not the one you want. (you have to have at least one visible, which is why you need to set one visible first)

 

[sulley333]

Do you know how to do the loop through?? Not sure what that would look like.

 

[RoryA]

Dim pi as pivotitem

With ActiveSheet.PivotTables("PivotTable1").PivotFields("Brand")
.PivotItems("Just For You").Visible = True
   for each pi in .PivotItems
      select case pi.name
         case "Just for You", "something else", "blah"
            pi.visible = True
         Case else
            pi.visible = False
      End Select
   Next pi
End With

 

[sulley333]

For some reason it doesn't seem to be working:

With ActiveSheet.PivotTables("PivotTable1").PivotFields("Brand")
.PivotItems(brand_one).Visible = True
For Each pi In .PivotItems
Select Case pi.Name
Case brand_one, brand_two
pi.Visible = True
Case Else
pi.Visible = False
End Select
Next pi

Where brand_one and brand_two are variables. Do you see something wrong with my code. It doesn't error out but nothing is showing for brand in pivot table.

 

[RoryA]

Well you should be getting an error or something displayed. Try converting the case:

With ActiveSheet.PivotTables("PivotTable1").PivotFields("Brand")
.PivotItems(brand_one).Visible = True
For Each pi In .PivotItems
Select Case UCASE(pi.Name)
Case ucase(brand_one), ucase(brand_two)
pi.Visible = True
Case Else
pi.Visible = False
End Select
Next pi

 

[sulley333]

When I hover over Pi.Name in the code it says the following

Pi.Name = ******** variable or with block variable not set>

Do you know what that might mean?

 

[RoryA]

Which line of code is actually running at the time? If you haven't entered the loop yet, then pi has no meaning.

 

[sulley333]

Sorry about that!! I just forgot to have this code that actually shows the field:

With ActiveSheet.PivotTables("PivotTable1").PivotFields("Brand")
.Orientation = xlColumnField
.Position = 1
End With

Duh! Thanks so much for all of your help!! I will be able to use this feature a lot!!! Have a great day!

 

[RoryA]

Yes, that will certainly improve things.
Glad to help.