Sub Reset()
Dim pt As PivotTable
Application.ScreenUpdating = False
RefreshSlicersOnWorksheet ActiveSheet
For Each pt In ActiveSheet.PivotTables
pt.RefreshTable
Next pt
If MsgBox("Do you want to refresh the PowerPivot Model?", vbYesNo) = vbYes Then
ActiveWorkbook.Model.Refresh
End If
Application.ScreenUpdating = True
End Sub
Public Sub RefreshSlicersOnWorksheet(ws As Worksheet)
Dim sc As SlicerCache
Dim scs As SlicerCaches
Dim slice As Slicer
Set scs = ws.Parent.SlicerCaches
If Not scs Is Nothing Then
For Each sc In scs
For Each slice In sc.Slicers
If slice.Shape.Parent Is ws Then
sc.ClearManualFilter
Exit For 'unnecessary to check the other slicers of the slicer cache
End If
Next slice
Next sc
End If
End Sub
Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.
”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.
Our community of experts have been thoroughly vetted for their expertise and industry experience.