VBA filter Pivot Table to only user selected value

I am trying to use VBA to filter a pivot table. My problem is that I want to make sure the only thing showing is the one item the user selected. How do I make sure to turn off all of the other values?

I have a range (CAList). When the user clicks on one of those cells, I want the "CA" field in the pivot table (PivotTable2) to be filtered to show the CA that the user clicked on. This is the code I have right now. I can see it cycling through and making all of the CA's invisible and then making the correct one visible, but the table on the sheet is always left with the last CA being visible.

Please Login or Register  to view this content.

I like the slicer, but my other problem is that I need to be able to store that value to use in another cell on the spreadsheet. With the VBA I have I can see what value the user clicked and put it in a cell where I can use it for other calculations. If I can pull the value of the slicer out and put it in a cell then I would be happy to use the slicer.

I have office 2010 and I do like using the slicer for something like this. Do you know the vba code to pull out the value of what the user has selected with the slicer? I've looked through several websites and I am not able to understand the code well enough to see how to pull the value of the slicer and put it into a cell.

I figured out a way to make this work. The problem with my original code is that a pivot table cannot be filtered to have nothing selected. Originally I was trying to clear the filter and then filter to only show the item the user had clicked. In the code below which works, I first filter to show everything, then I filter out each item except the one the user selected. Once I added in the code to stop the screen from showing the update process it looks great. This code does what I want but I would still be happy if someone let me know if this code could be improved.

Please Login or Register  to view this content.