Filtering Pivot Table Based on a Cell Value using Slicer to Change Cell Value

[jsepanski]

I am using the below code to update pivot another pivot table filter off a cell value that is chosen from a slicer on another sheet. It works but I have to go into the sheet and click on the cell range in A23 in order for it to update my other pivot tables. How to I get this to update pivot table 3 without clicking on the cell.. My end user will be using a slicer to change the data on the dashboard and this tab will be hidden so that pivot table will not update.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

'Update by Extendoffice 20180702
Dim xPTable As PivotTable
Dim xPFile As PivotField
Dim xStr As String
On Error Resume Next
If Intersect(Target, Range("a23:a28")) Is Nothing Then Exit Sub
Application.ScreenUpdating = False
Set xPTable = Worksheets("Calculatins").PivotTables("PivotTable3")
Set xPFile = xPTable.PivotFields("Skill")
xStr = Target.Text
xPFile.ClearAllFilters
xPFile.CurrentPage = xStr
Application.ScreenUpdating = True

 

[pjmorris]

I'm not sure I fully understand the question, but this seems a difficult way to apply a Slicer to multiple PivotTables. Assuming the source data is the same then right click the slicer and select 'Report Connections' then add the second pivot table to the list.

If the data sets are different I'd be thinking to add them to the data model and use PowerPivot. You could also think to use Powerquery to combine the information if that made sense, but do not be tempted to reduce all your data to a flat table - PowerPivot makes that requirement unnecessary.

Hope this helps.

 

[jsepanski]

Hi Peter thank you for your response. So why I have it like this is because I am also performing calculations between the two pivots and need just the one pivot table to pull by the SKILL that is selected from the slicer for the associate that will tie to the skill then the other pivot table is for all associates under the skill. So the one that is pulling from the cell value just needs to pull by the skill and not update all associates. I think the line of code that is causing me to actually click in the cell is this section:

If Intersect(Target, Range("a23:a28")) Is Nothing Then Exit Sub
I do not know how to make this be automatic and not have to click on the cell range for it to update

The slicer updates the value in the highlighted pivot and then I need the filter to update the top pivot.. It works only when clicking on the highlighted cell.

The skill slicer below changes when you select the asscoiate slicer.. Does this make more sense to my question.. Thanks for your help.. this is the last part that has me stuck on my dashboard.

 

[pjmorris]

Try using the Worksheet_Change(ByVal Target As Range) procedure rather than the Worksheet_SelectionChange(ByVal Target as Range). This should run whenever the value is changed and your Intersect test will only be true when it is the cell you're interested in; you'll no longer need to click in the cell to activate it. The procedure will run more often, but I doubt that will cause a problem for this.

 

[jsepanski]

Thanks Peter.. Do I need to update the intersect range or anything else. It looked like it was working when I changed it to the Change procedure but now its not pulling in the referenced cell value

 

[pjmorris]

I wouldn't have thought so - the Intersect function is merely to detect which cell has changed. Given that it currently checks 5 adjacent cells 'A23:A28' it might be as well to make sure that only the cell you're interested in is referenced. Alternatively/additionally I'd change the line 'xStr = Target.Text' to explicitly refer to the correct cell, so it might look like this 'xStr=Range("A23")' I can't test is so the exact code might be slightly different and it would be as well to include the worksheet name to ensure the data used is fully referenced:

'xStr=Worksheets("Name of Worksheet").Range("A23")'

Hope that helps.

 

[jsepanski]

Peter you are awesome!!! That worked.. I can't thank you enough for all your help!!!