Hi everyone,
I have vba code that connects slicers with different source of data. For some workbooks works but for my workbook it does not work. However, on my case, when I play the code for some reason the code is processing forever. It never stops. In the background it updates and it never stops. When I click escape it takes me to the code and I highlights this line siLong.Selected = False. I would appreciate if someone could look at this and help me to understand why it process the exercise for so long.
Option Explicit
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Dim wb As Workbook
Dim scMain As SlicerCache
Dim scTemp1 As SlicerCache
Dim scTemp2 As SlicerCache
Dim scTemp3 As SlicerCache
On Error GoTo errHandler
Application.ScreenUpdating = False
Application.EnableEvents = False
Set wb = ThisWorkbook
Set scMain = wb.SlicerCaches("Slicer_Country")
Set scTemp1 = wb.SlicerCaches("Slicer_Country1")
Set scTemp2 = wb.SlicerCaches("Slicer_Country2")
Set scTemp3 = wb.SlicerCaches("Slicer_Country3")
UpdateSlicer scMain, scTemp1
UpdateSlicer scMain, scTemp2
UpdateSlicer scMain, scTemp3
exitHandler:
Application.ScreenUpdating = True
Application.EnableEvents = True
Exit Sub
errHandler:
MsgBox "Could not update pivot table"
Resume exitHandler
End Sub
Sub UpdateSlicer(scShort As SlicerCache, scLong As SlicerCache)
Dim siShort As SlicerItem
Dim siLong As SlicerItem
scLong.ClearManualFilter
For Each siLong In scLong.VisibleSlicerItems
Set siLong = scLong.SlicerItems(siLong.Name)
Set siShort = Nothing
On Error Resume Next
Set siShort = scShort.SlicerItems(siLong.Name)
On Error GoTo errHandler
If Not siShort Is Nothing Then
If siShort.Selected = True Then
siLong.Selected = True
ElseIf siShort.Selected = False Then
siLong.Selected = False
End If
Else
siLong.Selected = False
End If
Next siLong
Exit Sub
errHandler:
MsgBox "Could not update pivot table"
End Sub
I have vba code that connects slicers with different source of data. For some workbooks works but for my workbook it does not work. However, on my case, when I play the code for some reason the code is processing forever. It never stops. In the background it updates and it never stops. When I click escape it takes me to the code and I highlights this line siLong.Selected = False. I would appreciate if someone could look at this and help me to understand why it process the exercise for so long.
Option Explicit
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Dim wb As Workbook
Dim scMain As SlicerCache
Dim scTemp1 As SlicerCache
Dim scTemp2 As SlicerCache
Dim scTemp3 As SlicerCache
On Error GoTo errHandler
Application.ScreenUpdating = False
Application.EnableEvents = False
Set wb = ThisWorkbook
Set scMain = wb.SlicerCaches("Slicer_Country")
Set scTemp1 = wb.SlicerCaches("Slicer_Country1")
Set scTemp2 = wb.SlicerCaches("Slicer_Country2")
Set scTemp3 = wb.SlicerCaches("Slicer_Country3")
UpdateSlicer scMain, scTemp1
UpdateSlicer scMain, scTemp2
UpdateSlicer scMain, scTemp3
exitHandler:
Application.ScreenUpdating = True
Application.EnableEvents = True
Exit Sub
errHandler:
MsgBox "Could not update pivot table"
Resume exitHandler
End Sub
Sub UpdateSlicer(scShort As SlicerCache, scLong As SlicerCache)
Dim siShort As SlicerItem
Dim siLong As SlicerItem
scLong.ClearManualFilter
For Each siLong In scLong.VisibleSlicerItems
Set siLong = scLong.SlicerItems(siLong.Name)
Set siShort = Nothing
On Error Resume Next
Set siShort = scShort.SlicerItems(siLong.Name)
On Error GoTo errHandler
If Not siShort Is Nothing Then
If siShort.Selected = True Then
siLong.Selected = True
ElseIf siShort.Selected = False Then
siLong.Selected = False
End If
Else
siLong.Selected = False
End If
Next siLong
Exit Sub
errHandler:
MsgBox "Could not update pivot table"
End Sub