Rowland Hamilton
Active Member
- Joined
- Nov 13, 2009
- Messages
- 250
How do I make this work?
I found this code online but it does not work for the section that deals with the exception for <>target.value that should force the pivots to equal (All).
Here is the section:
If pf.CurrentPage <> Target.Value Then
pf.CurrentPage = "(All)"
End If
If pf2.CurrentPage <> Target.Value Then
pf2.CurrentPage = "(All)"
End IfWhen I keep this section, The page fields stay as "(All)" even when I change the target cell value to another matching value in the pivot table items list (I'm using a drop down list 1-12 and (All)).
When I don't include this section, pivot tables change with the input in the cell until I choose an input that is not included in the field item list, in which case it stays with the last good input value. A better result than only one output, but Ideally I would like it to change for all valid inputs.
Full Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Dim pt2 As PivotTable
Dim pf2 As PivotField
Dim pi2 As PivotItem
Set pt = Sheets("Pivot").PivotTables("PivotTable1")
Set pt2 = Sheets("Pivot2").PivotTables("PivotTable1")
Set pf = pt.PivotFields("ARF")
Set pf2 = pt2.PivotFields("ARF")
If Target.Address = "$K$2" Then
For Each pi In pf.PivotItems
If pi = Target.Value Then
pf.CurrentPage = Target.Value
Exit For
End If
Next pi
For Each pi2 In pf2.PivotItems
If pi2 = Target.Value Then
pf2.CurrentPage = Target.Value
Exit For
End If
Next pi2
If pf.CurrentPage <> Target.Value Then
pf.CurrentPage = "(All)"
End If
If pf2.CurrentPage <> Target.Value Then
pf2.CurrentPage = "(All)"
End If
End If
End SubThank you, Rowland
I found this code online but it does not work for the section that deals with the exception for <>target.value that should force the pivots to equal (All).
Here is the section:
If pf.CurrentPage <> Target.Value Then
pf.CurrentPage = "(All)"
End If
If pf2.CurrentPage <> Target.Value Then
pf2.CurrentPage = "(All)"
End IfWhen I keep this section, The page fields stay as "(All)" even when I change the target cell value to another matching value in the pivot table items list (I'm using a drop down list 1-12 and (All)).
When I don't include this section, pivot tables change with the input in the cell until I choose an input that is not included in the field item list, in which case it stays with the last good input value. A better result than only one output, but Ideally I would like it to change for all valid inputs.
Full Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Dim pt2 As PivotTable
Dim pf2 As PivotField
Dim pi2 As PivotItem
Set pt = Sheets("Pivot").PivotTables("PivotTable1")
Set pt2 = Sheets("Pivot2").PivotTables("PivotTable1")
Set pf = pt.PivotFields("ARF")
Set pf2 = pt2.PivotFields("ARF")
If Target.Address = "$K$2" Then
For Each pi In pf.PivotItems
If pi = Target.Value Then
pf.CurrentPage = Target.Value
Exit For
End If
Next pi
For Each pi2 In pf2.PivotItems
If pi2 = Target.Value Then
pf2.CurrentPage = Target.Value
Exit For
End If
Next pi2
If pf.CurrentPage <> Target.Value Then
pf.CurrentPage = "(All)"
End If
If pf2.CurrentPage <> Target.Value Then
pf2.CurrentPage = "(All)"
End If
End If
End SubThank you, Rowland
Last edited: