BenChod
06-20-2017, 06:15 AM
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
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