VBA code to select filters in pivot table slicer

[PaulFogel123]

I've searched the web and there isn't much on this. On the attached sample file, I'd like a macro to select each slicer filter button in turn, one at a time, and insert code to execute a simple copy/paste/values following each slicer selection (I can modify it later). Then, I want to return the slicer to its original settings automatically. One more thing: I want it to execute upon opening the tab each time, without user input.

If I see how it's done I can modify the code to suit this and other applications of the same type. This would be a superb enhancement of what I'm working on.

Thanks, Paul

 

[dan_l]

idk how to do it with a slicer but if you just add a pivot table filter, the filter will live in a cell, and you can just pop in a macro whatevercell.value="blah blah blah" and it will do the filter without much problem. If it's a presentation layer issue I know you can also hide the row with the filter. The slicer should "look" like it's doing something at that point.

 

[Debaser]

You can do this kind of thing:

Sub LoopSlicerItems()
   Dim slc As SlicerCache
   Set slc = ThisWorkbook.SlicerCaches("Slicer_Product")
   With slc.SlicerItems
      Dim itemCount As Long
      itemCount = .Count
      Dim n As Long
      For n = 1 To itemCount
         slc.ClearAllFilters
         Dim j As Long
         For j = 1 To n - 1
            .Item(j).Selected = False
         Next j
         For j = n + 1 To itemCount
            .Item(j).Selected = False
         Next j
         
         Call copyPasteRoutineHere
      Next n
   End With
End Sub
Sub copyPasteRoutineHere()
   ' do your copy and paste here
End Sub

 

[PaulFogel123]

idk how to do it with a slicer but if you just add a pivot table filter, the filter will live in a cell, and you can just pop in a macro whatevercell.value="blah blah blah" and it will do the filter without much problem. If it's a presentation layer issue I know you can also hide the row with the filter. The slicer should "look" like it's doing something at that point.

Thank you, Dan. This method is straightforward and workable. I want it to execute automatically upon clicking on the tab. What's the easiest way? I also want it to reset to whatever the filter selections were before the macro executes. What do you suggest?

Thanks, Paul