VBA code to manage slicer items after selection crash constantly my excel software

[teuteux13]

Hello guys,

I am struggling with my dashboard and can't understand why i have a constant loop in my code when i select a slicer items. Then i have to restart my excel cause it crashes.

My goal is everytime i select one item slicer from my "Slicer_Industry" it triggers my module 5.

The code below is located in sheet3(customers) where is located my pivot table.

Option Explicit
 
Private Sub Worksheet_PivotTableChangeSync(ByVal Target As PivotTable)
Dim slItem As SlicerItem
    With ActiveWorkbook.SlicerCaches("Slicer_Industry")
       For Each slItem In .VisibleSlicerItems
       If slItem.Name = "Auto" Then
        Call Module1.Sort_Largest_to_Smallest_
        ElseIf slItem.Name = "Industrial" Then
          Call Module1.Sort_Largest_to_Smallest_
          ElseIf slItem.Name = "Wind Grease" Then
         Call Module1.Sort_Largest_to_Smallest_
       End If
       Next slItem
    End With
   End Sub

My module code is as per below

Option Explicit

Public Function GetListObjectForPT(pt As PivotTable) As ListObject
On Error Resume Next ' In case the Pivot isn't created from a ListObject
Set GetListObjectForPT = Range(pt.PivotCache.SourceData).ListObject
End Function

Sub Sort_Largest_to_Smallest_()
'Variance
Dim ws As Worksheet
Dim ws1 As Worksheet
Dim Rng As Range
Dim Rng1 As Range

Set ws = Worksheets("Customers")
Set Rng = ws.Range("B7:N2000")
Set ws1 = Worksheets("Customers")
Set Rng1 = ws1.Range("R7:Z2000")

Dim pt As PivotTable
Dim lo As ListObject
Dim pt1 As PivotTable
Dim lo1 As ListObject

Set pt = Worksheets("Customers").PivotTables("PivotTable1")
Set lo = GetListObjectForPT(pt)
Set pt1 = Worksheets("Customers").PivotTables("PivotTable2")
Set lo1 = GetListObjectForPT(pt1)

Rng.Sort Key1:=ws.Range("E7"), Order1:=xlDescending
Rng1.Sort Key1:=ws1.Range("U7"), Order1:=xlDescending

End Sub

Appreciate your support If you can help i am not a programmer at all. Just trying to help my teammate.

Thank you in advance.

BR

TiTi

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: VBA code to manage slicer items after selection crash constantly my excel. Need help.
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.