VBA Slicer SlicerItems Selected

ddander54

Board Regular
Joined
Oct 18, 2012
Messages
97
I have some VBA that I recorded and attached to a button that would set up the slicers for the person that was only needing to view their data. Each person who used the common workbook just clicked their macro and the sheets were filtered using the slicers for their specific needs. At the end of the 'slicer code', I was also then doing some copy paste of summed data to other locations in the worksheets to create graphs of their data. There are 3 slicers attached to a table and 3 slicers attached to a pivot table. The 2 sets of 3 slicers are making the same selections, but since you can't connect table slicers to pivottable slicers (yet!), I had to have VBA do it first to the table then to the pivot table. All worked well and good until I did my 3rd data refresh on the table....since the data is dynamic, that's when some of the SlicerItems were deleted and some new ones added, so the vba started throwing errors.

So for example Company 6 was deleted from the data refresh and Company 22 was added.....now that macro fails wherever I had listed SlicerItems("Company 6").Selected = True(or False) or doesn't filter correctly because I didn't tell it what to do with Company 22.

'This is how it was created using the recorded macro:
Code:
    With ActiveWorkbook.SlicerCaches("Slicer_Client1")
        .SlicerItems("Company1").Selected = True
        .SlicerItems("Company2").Selected = True
        .SlicerItems("Company3").Selected = True
        .SlicerItems("Company4").Selected = True
        .SlicerItems("Company5").Selected = False
        .SlicerItems("Company6").Selected = False
        .SlicerItems("Company7").Selected = False
        .SlicerItems("Company8").Selected = False
' ...etc and so on for the other slicers....

So, since the data is dynamic, this doesn't seem like I used the best method possible for them to filter their data in the macro. How could I have done this better with a macro so I don't have to train them how to use the slicers (and do the copy paste of data, etc), or so that I don't have to create 20 separate workbooks every day?

Thanks in advance,
Don
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Forum statistics

Threads
1,223,900
Messages
6,175,276
Members
452,629
Latest member
SahilPolekar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top