Update Pivot Table Filter Based on Value in Another Cell

[BigBadBarry]

Hi all (second thread this morning!),

I have managed to use some VB code (in Sheet2) to update a Pivot Table filter based on a value in my sheet (cell F1), pieced together from this forum.

What I can't do though is get F1 to reference another sheet (i.e. ='Sheet1'!C2) and update automatically. I have the VB in the worksheet with the Pivot table on, and obviously it doesn't run when I change cell C2 in Sheet 1.

I've tried to solve it myself using various sources and code in Sheet 1 or the module as well, but I am stuck. Any help would be greatly appreciated! The code in Sheet 2 is as follows:

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 = "Project"


On Error Resume Next
Application.EnableEvents = False
Application.ScreenUpdating = False


    If Target.Address = Worksheets("Sheet10").Range("F1").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

 

[Jerry Sullivan]

Hi BigBadBarry,

Try pasting this example into the Sheet Code module of the Sheet that has the F1 Cell that you want the Pivot filters to be based upon.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ws As Worksheet
    Dim pt As PivotTable
    Const strField As String = "Project"
    
    On Error Resume Next
    Application.EnableEvents = False
    Application.ScreenUpdating = False

    If Target.Address = "$F$1" Then
        For Each ws In ThisWorkbook.Worksheets
            For Each pt In ws.PivotTables
                With pt.PageFields(strField)
                    .ClearAllFilters
                    .CurrentPage = Target.Value
                End With
            Next pt
        Next ws
    End If

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

If you're using Excel 2010 or later, consider using Slicers as another way to sync PivotTable filters.

 

[BigBadBarry]

Thanks mate, that is perfect. I am using 2013 but Slicers didn't help me in this case - very long story but this solves my problems! Thanks you very much!

 

[rrocking]

Hi Jerry,

I am trying to run this code after pasting it in Sheet code module but its not happening. Is there anything else I need to enter in this code? How it will detect the target value from the required sheet?

 

[Jerry Sullivan]

Hi rrocking,
Did you modify the code to match your field name in the place of "Project" and the cell with the desired value in place of "$F$1".

If that doesn't fix things try adding a statement to display a message box with the address of the changed (Target) cell.
That will help narrow down the potential causes of this not working.

   Msgbox Target.Address
   If Target.Address = "$F$1" Then

 

[Danners]

Hi Jerry,

I'm hoping you might be able to help with my issue as well.

I am using this code in my dashboard to update pivot table filter for "month" which is a date value format mmm yy. But for some reason it will not work at all on this field. I'm guessing it is an issue with dates but I'm fairly new to VBA so not sure. I have tested the code on another text based filter "region" and it works absolutely fine on that but not for date. Any ideas?

Also the ultimate aim is actually to filter from two date options and provide the range so that all pivot tables are operating on the same date range filter i.e. From Mar 15 to Aug 15. However I have not been able to figure this step out, the above is as far as I have gotten.

Any help would be much appreciated.

As with bigbadbarry above users slicers are not an option as the dashboard is running normal graphs from a single date range on sheet1 and I have been asked to add to the dashboard with pivot tables - but the end user wants everything to operate from the two data validation drop downs on sheet 1.