Controlling Pivot Table Field Page-2
 

Dear All,

I hope someone can help me out. I am a VBA dummy but would like to do this.

There are 4 things I want to be able to do:

1. Cell $B$24 controls 2 pivot table field on the same page. (unlimited
specific would be great!!)

2. Cell $B$24 controls 2 pivot table field on different worksheets.

3. Pivot Table Field 1("Month Ended -----") controls pivot table field 2
("Month Ended") .

4. Cell $B$24 is a formula =Sheet1!A2 or a vlookup, and if this cell
changes, the pivot field also changes.


This is the script I have:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim pt As PivotTable
Dim pf As PivotField
Dim pf1 As PivotField
Dim pi As PivotItem

Set pt = Worksheets("Page 1-Summary").PivotTables(1)
Set pf = pt.PageFields("Month Ended" & "Month Ended -----") <--------- i
tried adding this...lol

Set pf1 = pt.PageFields()<--------------- i tried adding this..lol

If Target.Address = "$B$24" Then

Application.EnableEvents = False

For Each pi In pf.PivotItems
'For Each pi In pf1.PivotItems

If DateValue(pi.Value) = DateValue(Target.Value) Then
pf.CurrentPage = pi.Value


Exit For
End If
Next pi
Application.EnableEvents = True
End If

No obligation to answer all, at least 1 is another step closer.

Thanks!!!..........