(VBA) OLAP query with multiple selection

[Goan]

Hi,

I tried to find some information on this but my knowledge of VBA are extremely limited and I was not able to understand what I am doing wrong.

I have a Cube and I am trying to automate multiple selection on a field. The selection will be taken from a list.

The recorded code for multiple selection is the below:

Sub Macro1()

ActiveSheet.PivotTables("PivotTable2").PivotFields("[Game].[Game].[Game]").VisibleItemsList = Array("[Game].[Game].&[XYZ]", "[Game].[Game].&[ZYX]")

End Sub

I tried the below:

Sub Macro3()

str1 = "[Game].[Game].&[" & Worksheets("YYY").Range("O6") & "]"
str2 = str1 & """" & ", " & """[Game].[Game].&[" & Worksheets("YYY").Range("O7") & "]"

ActiveSheet.PivotTables("PivotTable2").PivotFields("[Game].[Game].[Game]").VisibleItemsList = Array("" & str2 & "")

End Sub

If I check the value of the str2 this looks correct but running the code gives me syntax error

I tried many things, can't post them all also to do not confuse further but another one, as example, was:

Sub Macro4()

str1 = "[Game].[Game].&[" & Worksheets("YYY").Range("O6") & "]"
str3 = "[Game].[Game].&[" & Worksheets("YYY").Range("O7") & "]"
str2 = str3 & """" & ", " & """" & str1

ActiveSheet.PivotTables("PivotTable2").PivotFields("[Game].[Game].[Game]").VisibleItemsList = Array("" & str2 & "")

End Sub

I tried removing or adding " here and there but still there is some errors.

If I try with just one string, that works:

Sub Macro4()

str1 = "[Game].[Game].&[" & Worksheets("YYY").Range("O6") & "]"

ActiveSheet.PivotTables("PivotTable2").PivotFields("[Game].[Game].[Game]").VisibleItemsList = Array("" & str1 & "")

End Sub

The part I am needing VBA to modify, in the original code, is "[Game].[Game].&[XYZ]", "[Game].[Game].&[ZYX]" as I would like that XYZ and ZYX (and eventually more) will be taken from a list, with a loop, as there will be a variable numbers of Game.

I think that the error may be in the ", " part but honestly I lost myself already long time ago and I am not really sure of what I am talking about.

Anyone is able to help?

Thank you very much for your time.