Why does the code below loop on end?

The goal is to sort the 3 pivot tables when the over/under slicer is selected. I have experimented with the under sort in the code below. It errors on the Pivottable(:MilesLoss") line.

Option Explicit

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)

If ActiveWorkbook.SlicerCaches("Slicer_Variance").SlicerItems("Over").Selected = True Then
    MsgBox "Over"
ElseIf ActiveWorkbook.SlicerCaches("Slicer_Variance").SlicerItems("Under").Selected = True Then

    Sheets("pdPIVOTS").Select
    ActiveSheet.PivotTables("TaskLoss20").PivotFields("Name").AutoSort xlAscending _
        , "Sum of Work Variance", ActiveSheet.PivotTables("TaskLoss20").PivotColumnAxis _
        .PivotLines(1), 1

        ActiveSheet.PivotTables("MileLoss20").PivotFields("Parent Milestone").AutoSort _
        xlAscending, "Sum of Work Variance", ActiveSheet.PivotTables("MileLoss20"). _
        PivotColumnAxis.PivotLines(1), 1

            ActiveSheet.PivotTables("TaskLoss20").PivotFields("Name").AutoSort xlAscending _
            , "Sum of Work Variance", ActiveSheet.PivotTables("TaskLoss20").PivotColumnAxis _
            .PivotLines(1), 1

Worksheets("Project Dash").Activate
End If

End Sub

1 answer

  • answered 2018-01-11 21:08 Mr k

    You can set to True the selected property to Over, if it loops, then finish.

    ActiveWorkbook.SlicerCaches("Slicer_Variance").SlicerItems("Over").Selected = True