Q
Qull666
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!!!..........
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!!!..........