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?
 
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

0
 
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
0
 
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
0
 
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..
0
 
rav_ravAuthor Commented:
Many thanks Saurabh
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.