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)):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: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 SubI'm a bit surprised your code's last line ends with: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
.VisibleItemsList = Array(pvtItems)
rather than just:
.VisibleItemsList = pvtItems
but I can't test here without the pivot table.