pivot talbe update macro for sheet - how do I add another pivot table in the code besides 1 extra?
Private Sub Worksheet_PivotTableUpdate _
(ByVal Target As PivotTable)
Dim wb As Workbook
Dim scShort As SlicerCache
Dim scLong As SlicerCache
Dim siShort As SlicerItem
Dim siLong As SlicerItem
On Error GoTo errHandler
Application.ScreenUpdating = False
Application.EnableEvents = False
Set wb = ThisWorkbook
Set scShort = wb.SlicerCaches("Slicer_MOEVM2")
Set scLong = wb.SlicerCaches("Slicer_MOEVM")
Set scLong = wb.SlicerCaches("Slicer_MOEVM1")
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
exitHandler:
Application.ScreenUpdating = True
Application.EnableEvents = True
Exit Sub
errHandler:
MsgBox "Could not update pivot table"
Resume exitHandler
End Sub
Private Sub Worksheet_PivotTableUpdate _
(ByVal Target As PivotTable)
Dim wb As Workbook
Dim scShort As SlicerCache
Dim scLong As SlicerCache
Dim siShort As SlicerItem
Dim siLong As SlicerItem
On Error GoTo errHandler
Application.ScreenUpdating = False
Application.EnableEvents = False
Set wb = ThisWorkbook
Set scShort = wb.SlicerCaches("Slicer_MOEVM2")
Set scLong = wb.SlicerCaches("Slicer_MOEVM")
Set scLong = wb.SlicerCaches("Slicer_MOEVM1")
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
exitHandler:
Application.ScreenUpdating = True
Application.EnableEvents = True
Exit Sub
errHandler:
MsgBox "Could not update pivot table"
Resume exitHandler
End Sub