Pivot Table Filter by Cell Value

M

Mystafet

Board Regular
Joined
Aug 5, 2008
Messages
53
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.

Code: 
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.

Code: 
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.
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Sort by date Sort by votes
Try:

Code: 
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
 
Upvote 0
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'
 
Upvote 0
In that case replace:

With Me.PivotTables("ManagerData")

with:

With Worksheets("Team UW Score") .PivotTables("ManagerData")
 
Upvote 0
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?
 
Upvote 0
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:
Code: 
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question
Filter Pivot, save as separate file, then email it
Replies
0
Views
291
skandall
S
B
  • Question
Update Pivot Tables with Refresh
Replies
1
Views
110
shift-del
shift-del
M
  • Solved
Conditional HYPERLINKS to worksheet tab based on a cell value
Replies
6
Views
350
smozgur
smozgur
Gringoire
  • Question
Filtering on a pivot by dates (with some constraints)
Replies
0
Views
316
Gringoire
Gringoire
T
  • Question
Change pivot table source via VBA?
Replies
0
Views
194
TheWennerWoman
T

Forum statistics

Threads
1,214,622
Messages
6,120,572
Members
448,972
Latest member
Shantanu2024

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top