VBA to Modify Pivot Filters

Hi There,

I have a workbook with three worksheets:
- Sheet1 - PivotTable1 listing Individual Performance under one layout
- Sheet2 - PivotTable2 listing Individual Performance under a second layout
- Sheet3 - List of specific individual names in column A

Is there VBA code that the experts can suggest that I can run to update PivotTable1 with filters that include specific employee names (listed in column A of Sheet3) and update PivotTable2 with filters that exclude those same names?

Below is the code I started with, and then I realized the experts would have a more efficient way

Sub ReportFiltering_SelectCoreIndividuals()

Dim pf As PivotField
Set pf = ActiveSheet.PivotTables("PivotTable1").PivotFields("IndividualName")
pf.ClearAllFilters
pf.EnableMultiplePageItems = True
   
For Each PivotItem In pf.PivotItems
    If PivotItem.Name = "JohnDoe1" Then
        PivotItem.Visible = False
    End If
Next

End Sub
rav_ravAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saurabh Singh TeotiaCommented:
I would recommend taking the above approach only..The reason for the same that if you notice when you a record a macro and uncheck the items..it shows the name for each item and set to false, However those items names are changing and which can lead to failure of the code

But the above method ensures that you are just seeing the item which is equal to your value and if its not found it will give you an error then...

Lets say you want to do this pivottable2 as well assuming it remain individual items only you can do this...

Sub ReportFiltering_SelectCoreIndividuals2()

Dim pf As PivotField
Set pf = Sheets("Sheet2").PivotTables("PivotTable2").PivotFields("IndividualName")
pf.ClearAllFilters
pf.EnableMultiplePageItems = True
   
For Each PivotItem In pf.PivotItems
    If PivotItem.value = "JohnDoe1" Then PivotItem.Visible = False
Next

End Sub

Open in new window


Saurabh
Saurabh Singh TeotiaCommented:
Or lets say if you want to show all the names which are their in sheet3 column -A then you can use this...

Sub ReportFiltering_SelectCoreIndividuals2()
Dim rng As Range
Set rng = Sheets("Sheet3").Range("A:A")

Dim pf As PivotField
Set pf = Sheets("Sheet2").PivotTables("PivotTable2").PivotFields("IndividualName")
pf.ClearAllFilters
pf.EnableMultiplePageItems = True
   
For Each PivotItem In pf.PivotItems
    If Application.WorksheetFunction.CountIf(rng, PivotItem.Value) > 0 Then PivotItem.Visible = False
Next

End Sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rav_ravAuthor Commented:
Saurabh,
Sorry, I had trouble following your earlier comment.
How would I modify the code you recently posted to deselect all the names in the pivot field first and then make the ones I want visible?
Thanks
Saurabh Singh TeotiaCommented:
rav,

The code will work other way arround when you play with pivot.. First in the code what im doing is im clearing any applied filters and making everything visible by this line:-

pf.ClearAllFilters

Now once everything is visible then i check each value at a time and uncheck those which i dont want to get the necessary answer that im looking for which is value equal to the search item that you are looking for...

Saurabh..
rav_ravAuthor Commented:
Many thanks Saurabh
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.