Pivot Table filter on a reference cell

[basima]

Hello everyone - please help. I have an expense report - with expenses for all employees. I have created one big pivot table where you the employee can filter on their employee ID. I would like to make it where they enter their ID on a seperate sheet and the pivot filters on that cell (the cell is the reference for the pivot table). thank youuuuuuuuuuuuuuuuuuuuuu

 

[cyrilbrd]

Hi Basima,

Have you tried this solution from Contextures?

Option Explicit
' Developed by Contextures Inc.
' www.contextures.com

Private Sub Worksheet_Change(ByVal Target As Range)

Dim ws As Worksheet
Dim pt As PivotTable
Dim pi As PivotItem
Dim strField As String

strField = "NAME OF THE FIELD YOU WISH TO FILTER MAYBE "ID"??"

On Error Resume Next
Application.EnableEvents = False
Application.ScreenUpdating = False
' Basima, place instead of "D2" the actual location of your Data Validation cell,
' that is the cell where the employee will choose their ID from the drop down list

    If Target.Address = Range("D2").Address Then
        
        For Each ws In ThisWorkbook.Worksheets
            For Each pt In ws.PivotTables
                With pt.PageFields(strField)
                    For Each pi In .PivotItems
                        If pi.Value = Target.Value Then
                            .CurrentPage = Target.Value
                            Exit For
                        Else
                            .CurrentPage = "(All)"
                        End If
                    Next pi
                End With
            Next pt
        Next ws
    
    End If

Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

It will require on Data Validation Cell (D2 in The code above) containing the ID list.

Hope this helps.

 

[basima]

Thank you for your reply. Should I just copy and paste this in the macro section as VBA code.

 

[Jerry Sullivan]

Hi basima,

Looks like cyrlbrd is offline, so I'll try to answer your question.

This code should be pasted into the Sheet Code Module of the Sheet that has the Cell with Data Validation (not a Standard Module).

You can get to that by Right-Clicking on that Sheet's Tab, then select View Code...

Two things to be aware of about this code example....
It will try to filter each PivotTable in each Sheet of your Workbook.
It assumes the Field that you are filtering is in the PageField area of the PivotTable (not the RowField section).

Just ask if you want something that will only filter one table or filter RowFields.

 

[basima]

Thank you both JS and cyrilbrd.

I have pasted the in the view code area of the sheet where the employee will enter their ID#. The pivot tables are on two seperate sheet. how can i run it. I pressed F8 and it doent work.

is there a way I can upload a file here.

Thank you

 

[Jerry Sullivan]

This forum doesn't support attachments- when necessary people either post to a sharing site like Box.com or exchange email addresses through a Private Message (PM).

At this point, it's probably not necessary to see your file in order to be able to help you.

If you will post the code that you have been trying to use, we can probably spot the problem.

 

[Jerry Sullivan]

Thank you both JS and cyrilbrd.

I have pasted the in the view code area of the sheet where the employee will enter their ID#. The pivot tables are on two seperate sheet. how can i run it. I pressed F8 and it doent work.

is there a way I can upload a file here.

Thank you

Re-reading your last post- it looks like you might be trying to run this like an ordinary macro using F8.

The code example is "Event" code and the macro is run automatically when a certain Event happens- in this case, when a change is made to the Worksheet where the employee enters their ID#.

Have you tried changing their ID# to see if that triggers the code?

 

[basima]

Thank you again. This is the code I have posted. i enter an ID and nothing happened.

Sub Update()

Dim ws As Worksheet
Dim pt As PivotTable
Dim pi As PivotItm
Dim strField("GID") As String GID is cell name where you do the drop down in the pivot table.
On Error Resume Next
Application.EnableEvents = False
Application.ScreenUpdating = False
If Target.Address = Range("Enter ID'!$C$2").Address Then (C2- is the location of the cell where they enter ID.

For Each ws In ThisWorkbook.Worksheets
For Each pt In ws.PivotTables
With pt.PageFields(strField)
For Each pi In .PivotItems
If pi.Value = Target.Value Then
.CurrentPage = Target.Value
Exit For
Else
.CurrentPage = "(All)"
End If
Next pi
End With
Next pt
Next ws

End If
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

 

[Jerry Sullivan]

It's not working since you have a mix of Event Code and Standard Macro- but we should be able to fix it.

1. For each PivotTable that you want to filter, what is the PivotTable Name, and what sheet is it on?

2. Is the Field "GID" to be filtered in the Report (PageField) area of the report, or the Row Labels area? (you can check this in the Field List pane if you aren't sure.)

 

[basima]

This is driving me crazy
The first pivot is on the sheet called "expense". The second pivot is on a sheet named "actuals". The GID field is in the pagefield are in the report section not the row labels area.

I have not named the pivots.

Thank you. You have no idea how much I appreciate this.