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:
' ...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
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
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