Pivot Table Filter by Cell Value

[Mystafet]

Hello All,
I did a search and found a few helpful threads, but nothing to actually do exactly what I need.

I'm working on a spreadsheet for manager's use, and I have run into an issue. I have all the data on a tab, and am using a general sheet that the managers can enter in their employee number, to see all their employees current scores.

It was requested that they have access to the data for each employee. I decided to just make a simple pivot based off their employee ID, that would update when they entered in their ID. This would allow them to open up the detail on a seperate tab by double clicking the pivot.

This is the code I'm currently using, yet I need it to refresh the pivot once the ID is entered into cell B2.

Sub Pivot1()
ActiveSheet.PivotTables("ManagerData").PivotFields("REVIEW_EMP_MGR_ID").CurrentPage = Range("B2").Value
End Sub

Any help would be greatly appreciated.



The below is the code I'm using to update another pivot table on a different tab, which is actually used to pull in each employees scores and such.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("B2")) Is Nothing Then Exit Sub
On Error Resume Next
Application.EnableEvents = False
Sheets("Manager PIVOT DATA").PivotTables("PivotManager").PivotCache.Refresh
Application.EnableEvents = True
End Sub

Unless there is some magical way to link a cell to a pivot table that will allow the manager to just click the individual employee ID to pull that specific data.

Any help would be greatly appreciated.

 

[Andrew Poulsom]

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("B2")) Is Nothing Then Exit Sub
    On Error Resume Next
    Application.EnableEvents = False
    Sheets("Manager PIVOT DATA").PivotTables("PivotManager").PivotCache.Refresh
    With Me.PivotTables("ManagerData")
        .PivotCache.Refresh
        .PivotFields("REVIEW_EMP_MGR_ID").CurrentPage = Target.Value
    End With
    Application.EnableEvents = True
End Sub

 

[Mystafet]

Thanks for the help Andrew, but that didn't seem to work. I'm guessing because the pivots I need refreshed are on different sheets and I don't see the sheet reference.


The Pivot 'PivotManager' is on the sheet 'Manager Pivot Data'

The Pivot 'ManagerData' is on the sheet 'Team UW Score'

 

[Andrew Poulsom]

In that case replace:

With Me.PivotTables("ManagerData")

with:

With Worksheets("Team UW Score") .PivotTables("ManagerData")

 

[Mystafet]

Thanks a ton for your fast help Andrew, that worked perfectly.

 

[Mystafet]

Ruh-roh, I ran into a new problem, once I got everything situated in my workbook. The sheet that the "ManagerData" pivot is on, is Protected, with the exception of cell B2.

When I update B2, the pivot does not update when the sheet is protected. Is there any way around this, or will I need to leave the sheet unprotected?

 

[Andrew Poulsom]

You can unprotect and reprotect the worksheet in your macro.

 

[Mystafet]

Thanks again Andrew. I got the Protect and Unprotect into the sheet now, and it's working great.

However, management just came to me and wants the pivot filter to read from numerous cells B7:B21, and E7:E21. These cells are populated once the pivot "PivotManager" is updated on sheet "Manager Pivot Data".

This would apply to the "ManagerData" Pivot on sheet "Team UW Score"

Apparently I need to learn VB, and I could probably at least play with it and figure something out.


Below is the code I'm using now:

Private Sub Worksheet_Change(ByVal Target As Range)
Sheets("Team UW Score").Unprotect Password:="miller"
    If Intersect(Target, Range("B2")) Is Nothing Then Exit Sub
    On Error Resume Next
    Application.EnableEvents = False
    Sheets("Manager PIVOT DATA").PivotTables("PivotManager").PivotCache.Refresh
    With Worksheets("Team UW Score").PivotTables("ManagerData")
        .PivotCache.Refresh
        .PivotFields("EMP_MGR_ID").CurrentPage = Target.Value
    End With
    Application.EnableEvents = True
Sheets("Team UW Score").Protect Password:="miller"
End Sub

 

[Mystafet]

morning bump for any direction

 

[Andrew Poulsom]

I don't understand what you mean by "read from numerous cells".