PIVOT table autofilter based on data validation cell selection

Hi,

I have a question about pivot tables

I attached a simplified example, question:

how can I link the value of the drop down box to the PIVOT table, so that the PIVOT tables shows the selection of the drop down box, without manually selecting it in the PIVOT table itself,

Thanks in advance

Using your example, right click on Sheet1 select View Code and paste below into resulting window.
Thereafter alterations to B2 should update the Pivot accordingly.

Note: when saving you must save in a macro enabled format and remember to enable Macros when re-opening going forward.

If using XL2010 (unknown) consider using a Slicer.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rngDV As Range
    On Error GoTo ExitPoint
    Application.EnableEvents = False
    Set rngDV = Range("B2")
    If Not Intersect(Target, rngDV) Is Nothing Then
        PivotTables("PivotTable1").PivotFields("Countries").CurrentPage = rngDV.Value
    End If
ExitPoint:
    Set rngDV = Nothing
    Application.EnableEvents = True
End Sub

Yes, but you need to provide some more information ... are you update all Pivots on Sheets 2 & 3 ?

The Change event will still reside on Sheet1 but obviously you need to iterate the Pivots on other sheets.

Roughly speaking:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rngDV As Range, ws As Worksheet, PT As PivotTable
    On Error GoTo ExitPoint
    Application.EnableEvents = False
    Set rngDV = Range("B2")
    If Not Intersect(Target, rngDV) Is Nothing Then
        For Each ws In Sheets(Array("Sheet2", "Sheet3"))
            For Each PT In ws.PivotTables
                With PT
                    On Error Resume Next
                    .PivotFields("Countries").CurrentPage = rngDV.Value
                    On Error GoTo ExitPoint
                End With
            Next PT
        Next ws
    End If
ExitPoint:
    Set rngDV = Nothing
    Application.EnableEvents = True
End Sub

however, this does not account for multi page selections etc... which may or may not be a requirement.

post a sample to demonstrate requirements as nec.

I believe the code provided previously does what you requested, however, you obviously need to modify the Sheets Array to reflect your own set up - I used Sheet2 & Sheet3 as these were referred to in prior post.

The code (provided previously and amended per above) should reside in Country Sheet Object in VBE (ie location of Data Validation cell)