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

 

[rafaelaslompo]

Hello everyone,

I have the exactly same problem, I have already tried a lot of codes, but they aren't working.
I also copied the exactly same code, save and closed excel, and still doesn't work.

I have a problem in the line:
.PivotItems(MyNames(i)).Visible = True


What i did:
Sub Test2()
Dim MyNames As Variant
Dim i As Long
Dim PI As PivotItem
MyNames = Array("ÖGP: DESERVE", "ÖGP: DESERVE_ECV", "ÖGP: DESERVE_ESV", "ÖGPS DA:DESERVE 15V1")
Sheets("Pivot Tables from CSIS Data").Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("DO_SHORT_TEXT")
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


Any help will be highly appreciated =)