vba does not reflect change in pivottable

[MartinL]

Morning all (well its morning here)

I have a pivot table that I need to make change from SUM to Average depending on what slicer button is selected.
Initially I had a few issues in that the code caused an endless loop, however I solved that by adding
Application.EnableEvents = False at the start and
Application.EnableEvents = True at the end of my code

When I initially wrote the code I used the record macro button and it worked perfectly
so I developed it further to check for all instances of the slicer and also to write back to a cell whether the results in the pivot table were Summed or Averaged. (I thought that made sence)

However, the VBA runs through, the slicer does its bit and the cell changes accordingly, however the pivot field does NOT change and for the life of me I cannot see why.

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Application.EnableEvents = False
    With ActiveWorkbook.SlicerCaches("Slicer_FILTER")
    If (.SlicerItems("Revenue").Selected = True) Then ActiveSheet.PivotTables("ManRep").PivotFields("VALUE ").Function = xlSum
    If (.SlicerItems("Revenue").Selected = True) Then Range("E57") = "Total"
    If (.SlicerItems("Packs Sold").Selected = True) Then ActiveSheet.PivotTables("ManRep").PivotFields("VALUE ").Function = xlSum
    If (.SlicerItems("Packs Sold").Selected = True) Then Range("E57") = "Total"
    If (.SlicerItems("Carts Sold").Selected = True) Then ActiveSheet.PivotTables("ManRep").PivotFields("VALUE ").Function = xlSum
    If (.SlicerItems("Carts Sold").Selected = True) Then Range("E57") = "Total"
    If (.SlicerItems("Stock Val").Selected = True) Then ActiveSheet.PivotTables("ManRep").PivotFields("VALUE ").Function = xlAverage
    If (.SlicerItems("Stock Val").Selected = True) Then Range("E57") = "Average"
    If (.SlicerItems("Stock Pack").Selected = True) Then ActiveSheet.PivotTables("ManRep").PivotFields("VALUE ").Function = xlAverage
    If (.SlicerItems("Stock Pack").Selected = True) Then Range("E57") = "Average"
    If (.SlicerItems("Stock Cart").Selected = True) Then ActiveSheet.PivotTables("ManRep").PivotFields("VALUE ").Function = xlAverage
    If (.SlicerItems("Stock Cart").Selected = True) Then Range("E57") = "Average"
    End With
ActiveSheet.PivotTables("ManRep").PivotCache.Refresh
Application.EnableEvents = True
End Sub

My guess is that the enable events is causing the problem

Any ideas

 

[MartinL]

OK This seemed to work, not very elegant but if someone knows how I can write it better I'd welcome the advise.

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Application.EnableEvents = False
    With ActiveWorkbook.SlicerCaches("Slicer_FILTER")
    If (.SlicerItems("Revenue").Selected = True) Then ActiveSheet.PivotTables("ManRep").PivotFields("VALUE ").Function = xlSum
    If (.SlicerItems("Revenue").Selected = True) Then Range("E57") = "Total"
    If (.SlicerItems("Revenue").Selected = True) Then Application.EnableEvents = True
    If (.SlicerItems("Revenue").Selected = True) Then Exit Sub
    
    If (.SlicerItems("Packs Sold").Selected = True) Then ActiveSheet.PivotTables("ManRep").PivotFields("VALUE ").Function = xlSum
    If (.SlicerItems("Packs Sold").Selected = True) Then Range("E57") = "Total"
    If (.SlicerItems("Packs Sold").Selected = True) Then Application.EnableEvents = True
    If (.SlicerItems("Packs Sold").Selected = True) Then Exit Sub
    
    If (.SlicerItems("Carts Sold").Selected = True) Then ActiveSheet.PivotTables("ManRep").PivotFields("VALUE ").Function = xlSum
    If (.SlicerItems("Carts Sold").Selected = True) Then Range("E57") = "Total"
    If (.SlicerItems("Carts Sold").Selected = True) Then Application.EnableEvents = True
    If (.SlicerItems("Carts Sold").Selected = True) Then Exit Sub
    
    If (.SlicerItems("Stock Val").Selected = True) Then ActiveSheet.PivotTables("ManRep").PivotFields("VALUE ").Function = xlAverage
    If (.SlicerItems("Stock Val").Selected = True) Then Range("E57") = "Average"
    If (.SlicerItems("Stock Val").Selected = True) Then Application.EnableEvents = True
    If (.SlicerItems("Stock Val").Selected = True) Then Exit Sub

    If (.SlicerItems("Stock Pack").Selected = True) Then ActiveSheet.PivotTables("ManRep").PivotFields("VALUE ").Function = xlAverage
    If (.SlicerItems("Stock Pack").Selected = True) Then Range("E57") = "Average"
    If (.SlicerItems("Stock Pack").Selected = True) Then Application.EnableEvents = True
    If (.SlicerItems("Stock Pack").Selected = True) Then Exit Sub
    
    If (.SlicerItems("Stock Cart").Selected = True) Then ActiveSheet.PivotTables("ManRep").PivotFields("VALUE ").Function = xlAverage
    If (.SlicerItems("Stock Cart").Selected = True) Then Range("E57") = "Average"
    If (.SlicerItems("Stock Cart").Selected = True) Then Application.EnableEvents = True
    If (.SlicerItems("Stock Cart").Selected = True) Then Exit Sub
    End With

End Sub