Multiple Pivot Table Page Field Link to spreadsheet Cell (All) Exception

[Rowland Hamilton]

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

 

[AlphaFrog]

It would be very helpful if you surrounded your VBA code with [ CODE] [/CODE] tags. Select your VBA code in the forum editor and then click on the pound # icon. It makes reading your code so much easier.

You already have example code (post #7) to display an error message if the user selects a month that is not available in the pivot table.

If you want to have the Data Validation Drop-down List only have the month numbers available in the data (column F), maybe try something like this...

=OFFSET($G$1,,,MONTH(MAX($F:$F)))

In this example...
Column F has the dates of your data,
G1 to G12 just have 1,2,3... 11,12