I am trying to automate date selection changes in a pivot table. Below is the code that I cannot seem to get to work. I have hard-coded the value for my variable "MYString" just for the purposes of testing. I have added some notes in the code to clarify. I am using Excel 2013.
Sub MOM_2()
Dim MYString As String
MYString = " Array("""", """", ""[Report Date].[Date].[Day].&[2018-10-01T00:00:00]"", ""[Report Date].[Date].[Day].&[2018-10-02T00:00:00]"", ""[Report Date].[Date].[Day].&[2018-09-01T00:00:00]"", ""[Report Date].[Date].[Day].&[2018-09-02T00:00:00]"")"
Debug.Print MYString
ActiveWorkbook.Sheets("MTD").PivotTables("PivotTable4").PivotFields( _
"[Report Date].[Date].[Year]").VisibleItemsList = Array("")
ActiveWorkbook.Sheets("MTD").PivotTables("PivotTable4").PivotFields( _
"[Report Date].[Date].[Quarter]").VisibleItemsList = Array("")
ActiveWorkbook.Sheets("MTD").PivotTables("PivotTable4").PivotFields( _
"[Report Date].[Date].[Month]").VisibleItemsList = Array("")
'If uncommented, the line below works.
'ActiveWorkbook.Sheets("MTD").PivotTables("PivotTable4").PivotFields("[Report Date].[Date].[Day]").VisibleItemsList = Array("", "", "[Report Date].[Date].[Day].&[2018-10-01T00:00:00]", "[Report Date].[Date].[Day].&[2018-10-02T00:00:00]", "[Report Date].[Date].[Day].&[2018-09-01T00:00:00]", "[Report Date].[Date].[Day].&[2018-09-02T00:00:00]")
'This is the line where I use my variable. When I check the value of the variable from debug.print it matches the line above exactly from the word Array to the end.
'In fact, if I copy the value of "MYString" from the immediate window and paste it over the word MyString it runs fine.
ActiveWorkbook.Sheets("MTD").PivotTables("PivotTable4").PivotFields("[Report Date].[Date].[Day]").VisibleItemsList = MYString
End Sub
Thanks in advance. I've been wracking my brain on this one and it is probably a simple fix.