Afternoon,

What i'm trying to do is two fold. I have three pivot tables on one sheet that correspond to their own unique data set.

However, 50% of the data in each Table has the same Name i.e EmployeeName, Month, Date.

I have been able to write the code so that a data validation list will filter all three pivot tables with the same value, however the problem I have is when I want to duplicate that same code to validate against another two values, Month and Year, the code no longer works.

The code I have for the Name is:

 Private Sub Worksheet_Change_Name(ByVal Target As Range)
'   Code goes in the Worksheet specific module
    Dim rng As Range
    Dim pt As PivotTable
    Dim i As Long
        
        '   Set Target Range, i.e. Range("A1, B2, C3"), or Range("A1:B3")
            Set rng = Target.Parent.Range("C7")
        '   Only look at single cell changes
            If Target.Count > 1 Then Exit Sub
        '   Only look at that range
            If Intersect(Target, rng) Is Nothing Then Exit Sub
        '   Action if Condition(s) are met (do your thing here...)
            
        Application.ScreenUpdating = False
            For Each ws In ActiveWorkbook.Worksheets
                ws.Unprotect SSMWord
            Next ws
            
            For i = 3 To 7
                For Each pt In Sheets(i).PivotTables
                    With pt.PivotFields("VerintName")
                        .ClearAllFilters
                        On Error Resume Next
                        .CurrentPage = rng.Value
                    End With
                Next pt
            Next i

End Sub
I'm struggling to get the code to work and validate the other two boxes. Any help is appreciated.


Bippa**