Need Assistance with Pivot Table Change

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.

Code:

---

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.

A guess, try:

Code:

---

x = 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]")
ActiveWorkbook.Sheets("MTD").PivotTables("PivotTable4").PivotFields("[Report Date].[Date].[Day]").VisibleItemsList = x

---

Quote:

---

Originally Posted by p45cal

A guess, try:

Code:

---

x = 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]")
ActiveWorkbook.Sheets("MTD").PivotTables("PivotTable4").PivotFields("[Report Date].[Date].[Day]").VisibleItemsList = x

---

---

Interesting. It works. The difference is that your method makes the variable a "Variant' Data Type by default since it is not being "Dim'ed". So by changing my declaration to make the variable a Variant data type and removing the double quotes it works. I would have thought that passing it as a string would have worked. Let me do just a little testing and then I will mark this as solved. Thank You!

Quote:

---

Originally Posted by p45cal

A guess, try:

Code:

---

x = 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]")
ActiveWorkbook.Sheets("MTD").PivotTables("PivotTable4").PivotFields("[Report Date].[Date].[Day]").VisibleItemsList = x

---

---

OK, so here is my question. If I hardcode it, it works and that is great. However, I need to be able to build the string dynamically based on how many days are elapsed in the month. I do that using two different variables. I need them to feed into the Array.

Code:

---

'So instead of this
x = 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]")
ActiveWorkbook.Sheets("MTD").PivotTables("PivotTable4").PivotFields("[Report Date].[Date].[Day]").VisibleItemsList = x

I need something like this where VIL and ItemString are my variables that I build:
x = Array(VIL & ItemString
ActiveWorkbook.Sheets("MTD").PivotTables("PivotTable4").PivotFields("[Report Date].[Date].[Day]").VisibleItemsList = x

---

x = Array(VIL & ItemString,VIL2 & ItemString2,VIL3 & ItemString3)

Quote:

---

Originally Posted by p45cal

x = Array(VIL & ItemString,VIL2 & ItemString2,VIL3 & ItemString3)

---

OK, thank you for helping me out. I wanted to post the entire function in case it might ever help anyone else. I am sure I could clean it up a bit and make it look better but I have spent way too much time already for what this is. I had to use the split function to break the string down into elements to populate the array. I am marking this as solved and moving on with my life.

Code:

---

Sub MoM_Arr()'
' Updates MTD Pivot to show correct dates for Month over Month comparison
'


Dim i As Long
Dim NUMDAYS As Integer
Dim ArrCnt As Integer
Dim VIL As Variant
Dim ItemString As Variant
Dim pvtItems As Variant
Dim CurYear As String
Dim CompYear As String
Dim CurMonth As String
Dim CompMonth As String
Dim MyDay As String




'Populate Date Variables
    NUMDAYS = ActiveWorkbook.Worksheets("Tools").Range("NUMDAYS")
    CurMonth = ActiveWorkbook.Worksheets("Tools").Range("MAXMONTH")
    CompMonth = ActiveWorkbook.Worksheets("Tools").Range("COMPMONTH")
    CurYear = ActiveWorkbook.Worksheets("Tools").Range("MAXYEAR")
    CompYear = ActiveWorkbook.Worksheets("Tools").Range("COMPYEAR")
   
'Create the strings for the array
    For i = 1 To NUMDAYS
        Select Case i
            Case Is = NUMDAYS
                VIL = VIL & "" & Chr(44) & Chr(32)
            Case Else
                VIL = VIL & "" & Chr(44) & Chr(32)
        End Select
    Next i




    For i = 1 To NUMDAYS
        Select Case i
            Case Is < 10
                MyDay = CStr(0 & i)
            Case Else
                MyDay = CStr(i)
        End Select
       
        Select Case Len(CurMonth)
            Case Is = 1
                CurMonth = CStr(0 & CurMonth)
            Case Else
                CurMonth = CStr(CurMonth)
        End Select
       
        ItemString = ItemString & "[Report Date].[Date].[Day].&[" & CurYear & "-" & CurMonth & "-" & MyDay & "T00:00:00]" & Chr(44) & Chr(32)
   
       
        Select Case Len(CompMonth)
            Case Is = 1
                CompMonth = CStr(0 & CompMonth)
            Case Else
                CompMonth = CStr(CompMonth)
        End Select
       
        ItemString = ItemString & "[Report Date].[Date].[Day].&[" & CompYear & "-" & CompMonth & "-" & MyDay & "T00:00:00]"
       
            Select Case i
                Case Is = NUMDAYS
                    'Dont add a delimiter at the end, this is the last element
   
                Case Else
                    ItemString = ItemString & Chr(44) & Chr(32)
                    Debug.Print ItemString
            End Select
    Next i


'Combine the array strings
    ItemString = VIL & ItemString
   
'Populate the array
    pvtItems = Split(ItemString, ", ")


'Update the pivot table to show the correct dates
    ActiveWorkbook.Sheets("MTD").PivotTables("PivotTable4").PivotFields("[Report Date].[Date].[Day]").VisibleItemsList = Array(pvtItems)


       
End Sub

---

You're creating a long string then splitting it. You can create the array directly. Try (this produces the same as your code (as far as I can tell)):

Code:

---

Sub MoM_Arr01()    '
' Updates MTD Pivot to show correct dates for Month over Month comparison
Dim i As Long, NUMDAYS As Integer, Posn As Long
Dim pvtItems() As Variant
Dim CurYear As String, CompYear As String, CurMonth As String, CompMonth As String, MyDay As String

'Populate Date Variables
With ActiveWorkbook.Worksheets("Tools")
  NUMDAYS = .Range("NUMDAYS")
  CurMonth = .Range("MAXMONTH")
  CompMonth = .Range("COMPMONTH")
  CurYear = .Range("MAXYEAR")
  CompYear = .Range("COMPYEAR")
End With
ReDim pvtItems(0 To 3 * NUMDAYS - 1)
CurMonth = Format(CurMonth, "00")
CompMonth = Format(CompMonth, "00")
Posn = NUMDAYS
'Create the strings for the array
For i = 1 To NUMDAYS
  MyDay = Format(i, "00")
  pvtItems(i - 1) = ""
  pvtItems(Posn) = "[Report Date].[Date].[Day].&[" & CurYear & "-" & CurMonth & "-" & MyDay & "T00:00:00]"
  pvtItems(Posn + 1) = "[Report Date].[Date].[Day].&[" & CompYear & "-" & CompMonth & "-" & MyDay & "T00:00:00]"
  Posn = Posn + 2
Next i
'Update the pivot table to show the correct dates
ActiveWorkbook.Sheets("MTD").PivotTables("PivotTable4").PivotFields("[Report Date].[Date].[Day]").VisibleItemsList = pvtItems
End Sub

---

but I think that perhaps you don't need so many "" items (they're all the same) so you might be able to get away with just one:

Code:

---

Sub MoM_Arr02()
' Updates MTD Pivot to show correct dates for Month over Month comparison
Dim i As Long, NUMDAYS As Integer
Dim pvtItems() As Variant
Dim CurYear As String, CompYear As String, CurMonth As String, CompMonth As String, MyDay As String
'Populate Date Variables
With ActiveWorkbook.Worksheets("Tools")
  NUMDAYS = .Range("NUMDAYS")
  CurMonth = .Range("MAXMONTH")
  CompMonth = .Range("COMPMONTH")
  CurYear = .Range("MAXYEAR")
  CompYear = .Range("COMPYEAR")
End With
ReDim pvtItems(0 To 2 * NUMDAYS)
pvtItems(0) = ""
CurMonth = Format(CurMonth, "00")
CompMonth = Format(CompMonth, "00")
'Create the strings for the array
For i = 1 To NUMDAYS
  MyDay = Format(i, "00")
  pvtItems(i * 2 - 1) = "[Report Date].[Date].[Day].&[" & CurYear & "-" & CurMonth & "-" & MyDay & "T00:00:00]"
  pvtItems(i * 2) = "[Report Date].[Date].[Day].&[" & CompYear & "-" & CompMonth & "-" & MyDay & "T00:00:00]"
Next i
'Update the pivot table to show the correct dates
ActiveWorkbook.Sheets("MTD").PivotTables("PivotTable4").PivotFields("[Report Date].[Date].[Day]").VisibleItemsList = pvtItems
End Sub

---

I'm a bit surprised your code's last line ends with:
.VisibleItemsList = Array(pvtItems)
rather than just:
.VisibleItemsList = pvtItems
but I can't test here without the pivot table.