0

I want to automatically select each item in the slicer, select the first item, copy the data and paste to another sheet, then select the next item in the slicer and copy the data and paste to another sheet, and so on until the last item in the slicer.

the vba code I use for copy and pasting is below:

Private Sub CommandButton1_Click()

Sheets("Sheet1").Select
Range("e3:f3").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial 
Paste:=xlPasteValues

End Sub
Vityata
  • 42,633
  • 8
  • 55
  • 100
MichaelMC
  • 11
  • 3
  • You probably don't need a slicer if you want to loop through all items in a specific field, and copy the pivot table data to a new tab each time. It'd be helpful if you shared some sample data. – BigBen May 24 '18 at 15:57
  • This will help you get started on the slicer settings IF you choose to take this route. You may find it easier to control the pivot table in comparison to the slicer. You can deselect all options for a field setting on a pivot table, but on a slicer you always have to have 1 selected which makes the process more difficult. Since a slicer controls a field setting, you are better off controlling that directly. https://stackoverflow.com/questions/50108890/vba-de-select-all-slicer-options-except-1/50109995?noredirect=1#comment87272522_50109995 – urdearboy May 25 '18 at 01:02

0 Answers0