Pivot Table Multiple Value Filter Help

[BenChod]

Hi All -

I created a pivot cache so I can used the same data set to create multiple pivot tables. As I add items to the page field, I want to filter on 3 items out of 10. I tried to use the pivot items set to visible for the three criteria I want to filter on and it's not filtering. If I set the pivot items to false on the criteria I want to filter out, that works. The problem I may face later on is what when the list to filter out items goes to 15 or 20. I don't want to write out each item to filter out. I would rather set the pivot items to be visible for the 3 I want to see. I have attached the snipped of the code and hopefully someone can provide a quick solution. The pivot item field is called "Phase Found In"

With ActiveSheet
            Lastrow = ActiveSheet.UsedRange.Rows.Count
        End With
Let copyrange = Lastrow + 8
        
    Set pt = pc.CreatePivotTable( _
        Tabledestination:=Range("A" & copyrange), _
        TableName:="OCE_SEV1_PIVOT")
        
    Set pf = pt.PivotFields("Severity")
    pf.Orientation = xlPageField
    pf.CurrentPage = "Severity 1"
    
    Set pf = pt.PivotFields("Blocking")
    pf.Orientation = xlPageField
    pf.CurrentPage = "Y"
    
    Set pf = pt.PivotFields("Stream")
    pf.Orientation = xlPageField
    pf.CurrentPage = "OCE"
    
    Set pf = pt.PivotFields("Status")
    pf.Orientation = xlPageField
    
    Set pf = pt.PivotFields("Phase Found In")
    pf.Orientation = xlPageField
    pf.EnableMultiplePageItems = True
    pf.ClearAllFilters
    pf.PivotItems("Assembly Test").Visible = False
    pf.PivotItems("Not a Testing Phase").Visible = False
    pf.PivotItems("Performance/Load Testing").Visible = False
    pf.PivotItems("Production Testing").Visible = False
    pf.PivotItems("PVT").Visible = False
    pf.PivotItems("Regression Testing").Visible = False
    pf.PivotItems("Unit Testing").Visible = False
       
    Set pf = pt.PivotFields("Assigned to App")
    pf.Orientation = xlRowField
    
    Set pf = pt.PivotFields("Assigned To Team")
    pf.Orientation = xlColumnField
    
    Set pf = pt.PivotFields("Assigned to App")
    pf.Orientation = xlDataField
    pf.Function = xlCount

[MINCUS1308]

I have experienced days of frustration while trying to filter pivot tables with macros - I know your pain.
You might try creating an array of your pivot items and then looping through the array and setting visibility for each item.

[MINCUS1308]

on a side note... the statement:

With ActiveSheet 
    Lastrow = ActiveSheet.UsedRange.Rows.Count 
End With

Is redundant.
you should be able to accomplish the same thing with

    Lastrow = ActiveSheet.UsedRange.Rows.Count

[BenChod]

Thanks for responding and I hear you. It's a real pain in the arse. Here's what I came up with based on your recommendation but I am getting an error: Compile error: Variable required - can't assign to this expression

 For Each pf.PivotItems In _
        pt.PivotFields("Phase Found In")
        Select Case pf.PivotItems
            Case "Integrated Systems Testing", "User Acceptance Test (UAT)", "End to End Testing (ETE)"
                pf.PivotItems.Visible = True
            Case Else
                pf.PivotItems.Visible = False
        End Select
    Next pf.PivotItems

[MINCUS1308]

ahh...
The debug is landing on your for statement - correct?
Not the let statement in your initial post?

A quick search led me here:
https://msdn.microsoft.com/VBA/Langu...his-expression

I think the increment on your loop is the problem.
Like I said I HATE WORKING WITH PIVOT TABLES because of stupid stuff like this

[BenChod]

Correct, the debug is landing on pf.PivotItems

[MINCUS1308]

I'm positive that there is a "correct" method to resolving your problem #GuruPleaseHelpMe!
As my tag line reads: I have no idea what I'm doing...

But there is always more than one way to skin a cat.
Somewhere you have data that is your "pivotitems".
You could step through the data and create an array of the items.
Then step through your array just as you tried to do with the pivot Items.
Its not pretty but that would work.

[Paul_Hossler]

Thanks for responding and I hear you. It's a real pain in the arse. Here's what I came up with based on your recommendation but I am getting an error: Compile error: Variable required - can't assign to this expression

 For Each pf.PivotItems In _
        pt.PivotFields("Phase Found In")
        Select Case pf.PivotItems
            Case "Integrated Systems Testing", "User Acceptance Test (UAT)", "End to End Testing (ETE)"
                pf.PivotItems.Visible = True
            Case Else
                pf.PivotItems.Visible = False
        End Select
    Next pf.PivotItems

The For Each loop is wrong

Not tested, but try something like this

Dim PI as PivotItem

For Each PI in In pt.PivotFields("Phase Found In") .PivotItems

    Select Case PI.Caption

    Case "Integrated Systems Testing", "User Acceptance Test (UAT)", "End to End Testing (ETE)" 
        PI.Visible = True 
    Case Else 
        PI.Visible = False 
    End Select 

Next

[Paul_Hossler]

Can you post a workbook with enough dummy data to show the issue?

This seems to be the area causing problems??

Set pf = pt.PivotFields("Phase Found In") 
pf.Orientation = xlPageField 
pf.EnableMultiplePageItems = True 
pf.ClearAllFilters 
pf.PivotItems("Assembly Test").Visible = False 
pf.PivotItems("Not a Testing Phase").Visible = False 
pf.PivotItems("Performance/Load Testing").Visible = False 
pf.PivotItems("Production Testing").Visible = False 
pf.PivotItems("PVT").Visible = False 
pf.PivotItems("Regression Testing").Visible = False 
pf.PivotItems("Unit Testing").Visible = False

I did a very simple Recorder macro and it seems to work so maybe something else is happening

Sub Macro1()
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("AAA")
        .EnableMultiplePageItems = True
        .CurrentPage = "(All)"
        .PivotItems("A").Visible = False
        .PivotItems("C").Visible = False
        .PivotItems("E").Visible = False
    End With
End Sub

Capture.JPG

[BenChod]

Thanks, that worked. Really appreciate the help. Now I don't have to list out all items that I want to exclude.