[VBA] Excel PivotTable Filtering

[ZombieHairdu]

With ActiveSheet.PivotTables("PivotTable1").PivotFields("Assignment Descriptor" _[INDENT])
.PivotItems("CCR Review").Visible = False
.PivotItems("CCR Review ").Visible = False[/INDENT]
End With

There are numerous other fields listed, about 25 in total

Question 1: Is there a way to just provide a wild card so that I don't have to include potentially having situations were they might have added 2 spaces at the end of the name?
Question 2: Lets say that I ONLY want to display the options "Manage HSC", "GAP Review", and "Other" from the PivotTable filter. Do I HAVE to call out every row and say false?

 

[fhqwgads]

for question 2
i think pivot items are numbered, so instead of

PivotItems("CCR Review").Visible = False

you can use

PivotItems(1).Visible = False

if CCR Review is the first item in the list
then just loop through all the items with something like
For i=1 to i=count of items
PivotItems(i).Visible = False
and add some line about excluding the ones you want to display

 

[ZombieHairdu]

I am really bad at VBA -- most of the content I have right now is through recording and editing the recording. I tried to make sense of it, but I genuinely don't even know what the code would look like.

 

[DanteAmor]

Question 1: Is there a way to just provide a wild card so that I don't have to include potentially having situations were they might have added 2 spaces at the end of the name?

Sub testPivot1()
  Dim pi As PivotItem
  Application.ScreenUpdating = False
  With ActiveSheet.PivotTables("PivotTable1").PivotFields("Assignment Descriptor")
    .ClearAllFilters
    For Each pi In .PivotItems
      Select Case True
        Case LCase(pi) Like LCase("CCR Review" & [B][COLOR=#0000ff]"*"[/COLOR][/B])
          pi.Visible = False
      End Select
    Next
  End With
End Sub

Question 2: Lets say that I ONLY want to display the options "Manage HSC", "GAP Review", and "Other" from the PivotTable filter. Do I HAVE to call out every row and say false?

Sub testPivot2()
  Dim pi As PivotItem
  Application.ScreenUpdating = False
  With ActiveSheet.PivotTables("PivotTable1").PivotFields("Assignment Descriptor")
    .ClearAllFilters
    For Each pi In .PivotItems
      Select Case LCase(pi)
        Case [COLOR=#0000ff]LCase("Manage HSC"), LCase("GAP Review"), LCase("other")[/COLOR]
        Case Else
          pi.Visible = False
      End Select
    Next
  End With
End Sub

Try the options and tell me.

 

[ZombieHairdu]

Perfect that worked absolutely flawlessly. Thank you Dante Amor.

My next quest is to figure out why when I use VBA to color the pivot table the color shows but when I unfilter it, the color disappears until I refilter it. Basically I filter the sheet down to for instance Manage HSC and then give those rows a specific color, then when everything has its specific color I unfilter to show the entire sheet which should show all the different colors but instead only shows white. Any ideas on that?

This is my color code..

'COLORS ON PIVOT TABLE
     ActiveSheet.PivotTables("Full Inventory").PivotFields("LOB").ClearAllFilters
     ActiveSheet.PivotTables("Full Inventory").PivotFields("LOB").CurrentPage = _
          "CNS DME"
     ActiveSheet.PivotTables("Full Inventory").PivotSelect _
          "'Assigned To' CNS DME" _
          , xlDataAndLabel + xlFirstRow, True
     With Selection.Interior
          .Pattern = xlSolid
          .PatternColorIndex = xlAutomatic
          .ThemeColor = xlThemeColorAccent2
     End With

 

[DanteAmor]

Perfect that worked absolutely flawlessly. Thank you Dante Amor.

Youre welcome, thanks for the feedback.

sorry, about the color I don't know how to help.