How Do I Change Two Pivot Table Page Selections with VBA Code

[dmarsh]

Hello,

I need to be able to select an item from two data validation dropdowns in
Sheet1 and have the pivot table in Sheet2 show those selections in the
corresponding Page fields.

For example, on Sheet1, my first dropdown selection in cell A1 may be
"Texas"; my second dropdown selection in cell A2 may be Fritos. Once I make
these selections, I need my Pivot Table on Sheet2 to reflect these selections.

Thank you in advance for your help!

 

[Patrick Molloy]

search this news group - there's been several similar questions.

Use the sheet's Change event. Right click the sheet tab and hit View Code
I've my dropdown on sheet1, cell D1 and my pivot is the only pt on sheet2
my validation changes the region field...

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
' check if PT region ...
If Target.Address = Range("D1").Address Then
selectPivotRegion Target.Value
Exit Sub
End If
End Sub

Sub selectPivotRegion(sRegion As String)
Dim pt As PivotTable
Dim pi As PivotItem
Set pt = Worksheets("Sheet2").PivotTables(1)
With pt.PivotFields("Region")
.PivotItems(sRegion).Visible = True
For Each pi In .PivotItems
pi.Visible = pi.Name = sRegion
Next
End With
End Sub