Filter a pivot table using VBA

[hshayhorn]

I have a pivot table that I create from a time reporting report I receive weekly. The report is the the entire company so I need to filter out all employees except my own. There are about 100 employees in the report but I only have 12. I recorded a macro that does this but the problem is if anyone leaves I get an error because that value is no longer there for the code to hide it. I can work around that by simply telling it to move on when there's an error. But that only masks the issue. The second problem is when a new person is added to the company. I now would have to identify the new person, add them to the code and run it again... Therem ust be a more dynamic way to hide everyone and then unhide or make visible my 12 people.

Even better would be for the code to look at a seperate excel file or .txt file for valid values and if the value is in the file then make that value visible in the pivot table. For now I will settle for a hide everything and unhide values I hardcode in the code. Here is a snipit of what I am doing.

With ActiveSheet.PivotTables("PivotTable1").PivotFields("Resource Name")
.PivotItems("A R A").Visible = False
.PivotItems("Abhilash").Visible = False
.PivotItems("Abrol").Visible = False
.PivotItems("Angadipeta").Visible = True
.PivotItems("Angra").Visible = False

Again, there are at least a hundred I need to hide and only 12 i need visible. Manually adding and removing the ones I need to hide will be very timeconsuming.


I'm using excel 2010

 

[Andrew Poulsom]

You could loop through the PivotItems, compare each one's Name with your list of 12 and hide/unhide as necessary.

 

[hshayhorn]

You could loop through the PivotItems, compare each one's Name with your list of 12 and hide/unhide as necessary.

Sounds like a great idea.... I have no idea how to do that.

 

[Andrew Poulsom]

See if you can adapt this by changing the array of names to suit:

Sub Test()
    Dim MyNames As Variant
    Dim i As Long
    Dim PI As PivotItem
    MyNames = Array("Name2", "Name6", "Name10")
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Resource Name")
        For i = LBound(MyNames) To UBound(MyNames)
            .PivotItems(MyNames(i)).Visible = True
        Next i
        For Each PI In .PivotItems
            PI.Visible = Not IsError(Application.Match(PI.Name, MyNames, False))
        Next PI
    End With
End Sub

 

[hshayhorn]

See if you can adapt this by changing the array of names to suit:

Sub Test()
    Dim MyNames As Variant
    Dim i As Long
    Dim PI As PivotItem
    MyNames = Array("Name2", "Name6", "Name10")
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Resource Name")
        For i = LBound(MyNames) To UBound(MyNames)
            .PivotItems(MyNames(i)).Visible = True
        Next i
        For Each PI In .PivotItems
            PI.Visible = Not IsError(Application.Match(PI.Name, MyNames, False))
        Next PI
    End With
End Sub

When I use the above code it keeps coking on the line:

.PivotItems(MyNames(i)).Visible = True

Not sure what might be wrong. I copied it exactly and only modified the names available.

 

[hshayhorn]

When I use the above code it keeps coking on the line:

.PivotItems(MyNames(i)).Visible = True

Not sure what might be wrong. I copied it exactly and only modified the names available.

OK so I don't know why but it's now working. I had to save the document first (I get it as an attached, emailed report) and then it runs fine. I think maybe it's not liking the use os PivotTable1? Could it be that PivotTable one is already used in memory? Can that be reset before the code runs?

 

[Andrew Poulsom]

You had PivotTable1 in your original post, so I assumed it existed. Maybe you can use:

With ActiveSheet.PivotTables(1).PivotFields("Resource Name")

 

[clide91]

I know this is an older post, but searching for answers to my very simnilar problem and I came across this. I have the exact same issue but on a larger scale. I have thousands of pivot items I want to hide and only five I want visible. The code suggested above works fine for me it's just a matter of it taking a long time to cycle through all of the pivot items. Looknig for a way then to bypass having to loop through every single one.
Thank you very much!

 

[Andrew Poulsom]

This may help you:

Daily Dose of Excel » Blog Archive » Filtering Pivots based on external ranges.

 

[jpyahu]

Excellent Example, It's help me a lot

Thanks you very much...