Writing Slicer Items to Sheet- Modifying Existing Code

Hey All!

Since Bakerman2 was kind enough to solve one of my long-running problems with the dashboard I'm working on, I have another to ask the community.

With the following code, I am able to capture the selected slicer filters and have them update in a cell on my sheet in real time.

My problem is that I have four slicers I want to do this with (each slicer's filters writing to a different cell), and I have not determined the way to do so yet.

My slicer names are: Slicer_Month, Slicer_TWP_Code, Slicer_Activity_Code, Slicer_Program_Code.

The cell formula for the first slicer would be:

Formula:

Please Login or Register  to view this content.

The Public function that makes it possible is:

Please Login or Register  to view this content.

I tried repeating this function four times with the slicer names but that resulted in Ambiguous name errors. I also tried inputting multiple slicer names into the slicercaches area of the sheet but that also did not work.

Any thoughts from my Excel Forum Heroes?

This line:
Set oSc = ThisWorkbook.SlicerCaches("Slicer_Month")
is hard coded. Since you pass the slicer name to the function change it to read
Set oSc = ThisWorkbook.SlicerCaches(SlicerName)

Hi dflak!

I ended up contacting the original code writer and he returned my question with an answer.

You are absolutely correct, however, (and I didn't realize it till his second solution) that "SlicerName" didn't mean "input your slicer name" ha.

I've been meaning to get back here to post the solution so I'm glad your reply sent me a reminder.

Here was his response:

Here is an alternative version which has an extra argument with which you can choose to either get all items in a single cell, or return a list of them. For the latter to work you must array-enter the formula into as many cells as you expect to have sliceritems.

Please Login or Register  to view this content.

Since I have four slicers, I entered this formula (below) into four cells (each with the correct slicer name) and then Array-Entered each one separately.

And Voila.

Formula:

Please Login or Register  to view this content.