Dear All,
I have a macro that loops through all values of a slicer and then performs an action (saving the sheet as another file).
The problem is that it takes ages (hours) to run even a single loop.
I suspect this is because each time an item is unselected in the slicer, Excel performs the slicer action.
Is there a way to avoid this?
Conceptually, could I run the action "for each slicer item, check if x, if yes select, if no unselect", and then redo the pivot table only at the end of that series of checks (and then export the sheet)?
Here is my code:
Sub LoopThroughSlicer()
Application.ScreenUpdating = False
Dim sI As SlicerItem, sI2 As SlicerItem, sC As SlicerCache
'' Define Slicer to loop through
Set sC = ActiveWorkbook.SlicerCaches("Slicer_Inventory")
'' Begin Loop
With sC
For Each sI In sC.SlicerItems
sC.ClearManualFilter
For Each sI2 In sC.SlicerItems
If sI.Name = sI2.Name Then sI2.Selected = True Else: sI2.Selected = False
Next
' Action to perform at each loop here (e.g., export sheet)
Next
End With
Application.ScreenUpdating = True
End Sub
Any help is appreciated. Thanks!
as
Bookmarks