Item could not be found in the OLAP cube when using a variable

[Ellemieke]

Hi,

I've got a workbook containing several pivot tables. I try to build a macro, so I can update outlook versions for all the tables at the same time. I have declared the outlook as a variable and inserted it into the code where the hard coded outlook used to be, but when I run the code, I get the error 'Item could not be found in the OLAP cube'. This is the code I've got:

Dim stroutlook As String
stroutlook = Range("S7")


ActiveSheet.PivotTables("PivotTable2").PivotFields( _
"[Forecast Type].[Forecast Type].[Forecast Type Level 1]").VisibleItemsList = _
Array("")
ActiveSheet.PivotTables("PivotTable2").PivotFields( _
"[Forecast Type].[Forecast Type].[Forecast Type Level 2]").VisibleItemsList = _
Array("")
ActiveSheet.PivotTables("PivotTable2").PivotFields( _
"[Forecast Type].[Forecast Type].[Forecast Type Level 3]").VisibleItemsList = _
Array( _
"[Forecast Type].[Forecast Type].[Forecast Type Level 3].& [ " & stroutlook & " ] &[4022]&[4000]" _
)

Hardcoding the outlook to "OUTLOOK_2022_10" does work. What am I missing here?

 

[RoryA]

You appear to have spaces in there (before/after the square brackets) that probably shouldn't be there, but you also haven't specified the sheet for the Range("S7") property, which is rarely a good idea.

 

[Ellemieke]

Yes! That is the solution. I thought that spaces didn't really matter. Apparently they do. Thank you very much!