none
Pivot Table Page Field Linked to List - Issue with (All) RRS feed

  • Question

  • Hi,

    Need some help trying to link 4 different pivots to have the same page field as a cell in the worksheet.

    I found a solution but it does not let me choose the (All) option. Any way for me to be able to have the cell reference (All) and enable the page fields to be updated as such?

    Thanks in advance.

    My current code: (found online and posted by ddalgleish)

    For example, if the dropdown is in cell C2, and the pivot
    table is on a sheet named Sales Pivot, right-click on the sheet tab for
    the data validation sheet and add the following code:

    '========================
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim pt As PivotTable
    Dim pf As PivotField
    Dim pi As PivotItem
    Set pt = Worksheets("Sales Pivot").PivotTables(1)
    Set pf = pt.PageFields("Region")

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

    Application.EnableEvents = False
    For Each pi In pf.PivotItems
    If LCase(pi.Value) = LCase(Target.Value) Then
    pf.CurrentPage = pi.Value
    Exit For
    End If
    Next pi
    Application.EnableEvents = True

    End If

    End Sub

    Wednesday, January 21, 2015 3:20 AM

Answers

  • Hi KingTCat,

    The problem in your code is that if you select "(All)" in cell "C2", the pf.CurrentPage property will not be set a value, because the pf.PivotItems don't contain a value "(All)", so the page will not be set properly. You just need a small change for your code. Like this:

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim pt As PivotTable
    Dim pf As PivotField
    Dim pi As PivotItem
    Set pt = Worksheets("Sales Pivot").PivotTables(1)
    Set pf = pt.PageFields("ClassName")
    
    If Target.Address = "$C$2" Then
    
       Application.EnableEvents = False
       For Each pi In pf.PivotItems
          If LCase(pi.Value) = LCase(Target.Value) Then
              pf.CurrentPage = pi.Value
              Exit For
          End If
       Next pi
       
       If LCase(Target.Value) = "(all)" Then
           pf.CurrentPage = "(All)"
       End If
       Application.EnableEvents = True
    
    End If
    
    End Sub
    


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, January 23, 2015 3:04 AM
    Moderator