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]

Can you record a new macro where you change the page field "ARF" to (ALL) and then post that code?

 

[Rowland Hamilton]

Here is the recorded macro code for changing the pivot field to "(All)":

ActiveSheet.PivotTables("PivotTable1").PivotFields("ARF").CurrentPage = "(All)"

In the previous code, the Pivot Fields are defined as pf and pf2. The exception code uses pf.CurrentPage = "(All)" just like the recorded code does:

If pf.CurrentPage <> Target.Value Then
pf.CurrentPage = "(All)"
End If

Thank you

 

[AlphaFrog]

This worked for me....

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Target.Address = "$K$2" Then
    
        With Sheets("Pivot").PivotTables("PivotTable1").PivotFields("ARF")
            .CurrentPage = "(All)"
            On Error Resume Next
                .CurrentPage = Target.Value
            On Error GoTo 0
        End With
        
        With Sheets("Pivot2").PivotTables("PivotTable1").PivotFields("ARF")
            .CurrentPage = "(All)"
            On Error Resume Next
                .CurrentPage = Target.Value
            On Error GoTo 0
        End With

    End If
    
End Sub

 

[Rowland Hamilton]

That works, Thanks.

This version defines no terms and still works. I wonder why the other way didn't when you added the "(All)" exception? I really just wanted it to accept "(All)" as a selection choice.

Also, erhaps give an error message when a selection was made that was not included as a pivot item in the pivot field. But that might be too complicated.

For example, I have months 1-12 and "(All)" as choices in a data validation drop down list in target.address = $K$2. But my pivots only have data for months 1-10 and "(All)". Your solution, makes "(All)" the default if month 11, 12 or "(All)" is chosen. The old method, without the "(All)" exception, keeps the last good input 1-10, but will not change if 11, 12 or "(All)" are chosen.

If 1-10 will work normal, and "(All)" needs the exception to work, could another exception be coded to give an error message when 11 or 12 are selected (and remember, when there is data for 11 and/or 12, the exception will no longer be valid)?

 

[AlphaFrog]

This is a guess.

In your original code, I think you are comparing numbers (the K2 value) with text (the .CurrentPage value).

I think this might work in your original code...

If pf.CurrentPage <> Target[COLOR="Red"].Text [/COLOR]Then
pf.CurrentPage = "(All)"
End If
If pf2.CurrentPage <> Target[COLOR="Red"].Text[/COLOR] Then
pf2.CurrentPage = "(All)"
End If

 

[AlphaFrog]

There are different ways to error trap. Here's one way to display a message if the Page field can't be selected.

    If Target.Address = "$K$2" Then

        With Sheets("Pivot").PivotTables("PivotTable1").PivotFields("ARF")
            .CurrentPage = "(All)"
            Err.Number = 0
            On Error Resume Next
                .CurrentPage = Target.Value
            On Error GoTo 0
            If Err.Number <> 0 Then MsgBox "Can't select page item " & Target.Text & " on worksheet: ""Pivot""."
        End With

        With Sheets("Pivot2").PivotTables("PivotTable1").PivotFields("ARF")
            .CurrentPage = "(All)"
            Err.Number = 0
            On Error Resume Next
                .CurrentPage = Target.Value
            On Error GoTo 0
            If Err.Number <> 0 Then MsgBox "Can't select page item " & Target.Text & " on worksheet: ""Pivot2""."
        End With

    End If

 

[Rowland Hamilton]

AlphaFrog:

You started off coy, but you were holdin' out on some skills. Haven't tried your new suggestions, yet, but Thanks! - Rowland

 

[Rowland Hamilton]

Note - I tried:


If pf.CurrentPage <> Target.Text Then
pf.CurrentPage = "(All)"
End If
If pf2.CurrentPage <> Target.Text Then
pf2.CurrentPage = "(All)"
End If</pre>
once this portion of the code is activated, the results stay the same regardless of the input in the target cell $K$2 and no other input is registered.

 

[Rowland Hamilton]

<!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} </style> <![endif]--> I used your code:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$C$2" Then

With Sheets("MTDpivot").PivotTables("PivotTable1").PivotFields("Mo")
.CurrentPage = "(All)"
On Error Resume Next
.CurrentPage = Target.Value
On Error GoTo 0
End With

With Sheets("MTDpercent").PivotTables("PivotTable1").PivotFields("Mo")
.CurrentPage = "(All)"
On Error Resume Next
.CurrentPage = Target.Value
On Error GoTo 0
End With

End If

End Sub

Cell $C$2 on the worksheet utilizes a drop down menu that allows for the selection of months 1 through 12 and "(All)".

New Question: What is the best way to prevent the selection or send a warning message if the month chosen is later than the months in the pivot table's current data source?

For example: On Mar 3rd, I update the pivots source data with February data adding to the January data. I go to my cell $C$2 on the special tab and if I select Month "3", it will show as month "3" on the special tab but as "(All)" in the pivots. How can I let the user know that they should not choose from Months 3-12, yet.

Should the Macro just look at the pivots current data source, review all the dates in the date column F and determine the oldest month, or review the months in Month Column L (derived, with a formula in the sheet, from Column F dates)? If so, how do I write that?

Or would it be less volatile to designate another cell in the workbook to add the month for the current analysis. Of course, this would not make it much different than realizing to put the right month into the drop down in the first place.

I considered the formula

=Month(Today())-1

, but I do not want to use macros that are not dynamic for this and if it is dynamic, then the file would only be correct during the month following the last month of source data.