Loop through Slicer for Data Model Pivot Table

[rilzniak]

Hey all,

I'm struggling with syntax here, and I'm sure it's something simple. I'm trying to loop through the slicer I have for a Pivot Table but I can't figure out how to change the second part of this statement so that it varies as I loop through the options and print out statements for reporting.

    sLocCode= mWB.Worksheets(mSheet).Cells(i, 2).Value

    ActiveWorkbook.SlicerCaches("Slicer_Location_Code").VisibleSlicerItemsList = Array("[Quantity].[Location Code].&[09]")

The sLocCode comes from a different workbook and will replace the numbers in the square brackets - "09". Can someone help explain how can I re-write the second part of that statement to accept a variable so that I'm able to loop through the different location codes? The quotations are throwing me for a loop (<-- see what I did there?).

Thanks in advance.

 

[RoryA]

<-- see what I did there?

It would be:

 ActiveWorkbook.SlicerCaches("Slicer_Location_Code").VisibleSlicerItemsList = Array("[Quantity].[Location Code].&[" & sLocCOde & "]")

 

[rilzniak]

It would be:

 ActiveWorkbook.SlicerCaches("Slicer_Location_Code").VisibleSlicerItemsList = Array("[Quantity].[Location Code].&[" & sLocCOde & "]")

Sigh. That was so simple. I was trying to keep the original double quotations in the statement (like an idiot!). Might have to send myself home early today, that was just so poor on my part.

Thank you, Rory. A+

 

[RoryA]

Glad we could help.