Pivot Table filtering with Macro

[Linda Crasco]

Hello All,

I do a monthly report consisting of five data tables (each with different fields). A common element is the staff. Each month different members of the 20 member staff show up in different tables. For example, in June Joe may be in data tables 1, 2, 3, 4, and 5, but in July he may be only in data tables 3 and 4.
I have set up Pivot tables with slicers for each of the data tables.
The managers want to be able to filter for each member of the staff all at once. I have made macros and assigned them to buttons so when the manager clicks on the “Joe” button all five pivot tables filter for Joe.
The problem is each month I have to adjust my macro to add staff who are in a data table this month that they were not in last month, and delete staff from data tables that they were in last month, but not this month. I want to list all the possible staff in the macro, and have the macro skip the non applicable staff and resume. The on error resume command does not work.
Currently:
Sub Joe()
'
' Joe Macro
'
'
With ActiveWorkbook.SlicerCaches("Slicer_Data Table 1")
.SlicerItems("Howard").Selected = False
.SlicerItems("Jeffery").Selected = False
.SlicerItems("Mark").Selected = False
.SlicerItems("Marlene").Selected = False
.SlicerItems("Joe").Selected = True
.SlicerItems("Sharon").Selected = False
.SlicerItems("Steven").Selected = False
.SlicerItems("Mary").Selected = False
.SlicerItems("Tom ").Selected = False
.SlicerItems("Tim").Selected = False
End With
With ActiveWorkbook.SlicerCaches("Slicer_Data Table 2")
.SlicerItems("Howard").Selected = False
.SlicerItems("Lisa").Selected = False
.SlicerItems("Mark").Selected = False
.SlicerItems("Marlene").Selected = False
.SlicerItems("Joe").Selected = True
.SlicerItems("Sharon").Selected = False
.SlicerItems("Steven").Selected = False
.SlicerItems("Mary").Selected = False
End With
End Sub
Want:
With ActiveWorkbook.SlicerCaches("Slicer_Data Table 1")
.SlicerItems("Howard").Selected = False
.SlicerItems("Lisa").Selected = False (Lisa is not in Data Table 1 so macro skips Lisa and goes to Jeffrey)
.SlicerItems("Jeffery").Selected = False
.SlicerItems("Mark").Selected = False
.SlicerItems("Marlene").Selected = False
.SlicerItems("Joe").Selected = True
.SlicerItems("Sharon").Selected = False
.SlicerItems("Steven").Selected = False
.SlicerItems("Mary").Selected = False
.SlicerItems("Tom ").Selected = False
.SlicerItems("Tim").Selected = False
End With
With ActiveWorkbook.SlicerCaches("Slicer_Data Table 2")
.SlicerItems("Howard").Selected = False
.SlicerItems("Lisa").Selected = False
.SlicerItems("Jeffery").Selected = False (Jeffrey is not in Data Table 2 so macro skips Jeffery and goes to Mark)
.SlicerItems("Mark").Selected = False
.SlicerItems("Marlene").Selected = False
.SlicerItems("Joe").Selected = True
.SlicerItems("Sharon").Selected = False
.SlicerItems("Steven").Selected = False
.SlicerItems("Mary").Selected = False
.SlicerItems("Tom ").Selected = False (Tom is not in Data Table 2 so macro skips Tom and goes to Tim
.SlicerItems("Tim").Selected = False (Tim is not in Data Table 2 so macro skips Tim and goes to next Table 3 (not shown in example)
End With
End Sub
I hope I have explained it adequately. Thank you in advance for any help you can give me.
~Linda

 

[jeffreyweir]

Linda, it's much easier for someone to help if you upload a sample data file. Particularly where PivotTables and Slicers are concerned. That way it's not only easier for us to conceptualise what your setup is like, but easier for us to step through any actual code and see what's happening.

 

[NARAYANK991]

Hi Linda ,

To add to what Jeff has posted , instead of having multiple statements to deselect the corresponding slicer items , you can have one statement :

ActiveWorkbook.SlicerCaches("Slicer_Data Table 1").ClearManualFilter

After this statement , you follow it up with selecting the slicer item you wish to filter on.

Narayan

 

[jeffreyweir]

Hi Narayan. Nice to bump into you.

To add something more, a problem with this is if you try to select something in the Slicer that doesn't exist. If you have an On Error Resume next in there, the code will simply skip that line, the PivotTable won't be filtered on anything, and your users may not be aware that this is the case.

How I've got around this in the past is to actually hide the body of the pivottable from Users in that case.

Uploading a sample file would help us give alternatives to cover things like this.

 

[Linda Crasco]

Thank you- I will upload a file- I have to strip it of identifing information first. Jeffreyweir is correct- my problem is when I try to select something that is not there the macro stops. I could not get the on error resume to work.

I did use Narayan's solution to clear the filters instead of deseleting one by one- ActiveWorkbook.SlicerCaches("Slicer_Data Table 1").ClearManualFilter- which worked great- half my problem is solved. Thank you Narayan!