Controling a Pivot Dropdown

[Steve]

I have an excel workbook with 2 worksheets named Pivot & Summary.

The Pivot worksheet contains a large & cumbersome pivot table which contains
dated information. One of the page dropdowns on the pivot table is a month
selector.

The summary worksheet picks off relevant data from the pivot table on the
Pivot worksheet and displays in an easy to read format.

As it is, to view a different month's data, I have to select the Pivot
worksheet, change the month and then go back to the summary worksheet to view
the change.

My question is, can I control or change the Month drop down control from the
Summary worksheet?

 

[Tom Hutchins]

One way...

On the Summary sheet, create a dropdown list with ALL the valid selections
for the Month page field - probably (all) plus every month. In my example, I
used data validation and put this dropdown list in cell A1.

Right-click on the (Summary) sheet tab and select 'View code'. The Visual
Basic Editor will open. Paste the following code in the big blank white
window:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
If Len(Target.Value) = 0 Then Exit Sub
Sheets("Pivot").PivotTables(1).PivotFields("Month").CurrentPage = _
Sheets("Summary").Range("A1").Value
End Sub

Change "A1" to the cell where you added the dropdown list. Save the
workbook, then press Alt-Q to close the Visual Basic Editor and return to
regular Excel. Now the page field in the pivot table on the Pivot sheet
should change whenever you change the selection in the dropdown cell on the
Summary sheet.

Hope this helps,

Hutch

 

[Steve]

Thanks Tom . . . . .Worked exactly as I requested!!