Filter a pivot table using VBA

hshayhorn

New Member
Joined
Aug 1, 2013
Messages
12
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
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
You could loop through the PivotItems, compare each one's Name with your list of 12 and hide/unhide as necessary.
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
See if you can adapt this by changing the array of names to suit:

Code:
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

New Member
Joined
Aug 1, 2013
Messages
12
See if you can adapt this by changing the array of names to suit:

Code:
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

New Member
Joined
Aug 1, 2013
Messages
12
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
You had PivotTable1 in your original post, so I assumed it existed. Maybe you can use:

Rich (BB code):
With ActiveSheet.PivotTables(1).PivotFields("Resource Name")
 

clide91

New Member
Joined
Apr 22, 2014
Messages
5
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,099,057
Messages
5,466,321
Members
406,474
Latest member
osama beskales

This Week's Hot Topics

Top