title: Excel 360 VBA to change pivot table filter
body: <p>I am trying to change the filter on a pivot table that was set up using the "Add this data to the Data Model" functionality using VBA which would read the selection of a list box containing the items to filter on. I started by recording a macro to see what would happen and was given this:</p>
  
  <pre><code>ActiveSheet.PivotTables("Pivottable1").PivotFields("[Range 1 1].[Quarter].[Quarter]").VisibleItemsList = _
      Array("[Range 1 1].[Quarter].&amp;[1Q20]", "[Range 1 1].[Quarter].&amp;[3Q20]")
  </code></pre>
  
  <p>My problem is to set up the array dynamically which could contain any number of items. I need to do something like the below but appending to the array each time which this doesn't do:</p>
  
  <pre><code>For x = 0 To ListBox1.ListCount - 1
      If ListBox1.Selected(x) = True Then
         myArray = Array("[Range 1 1].[Quarter].&amp;[" &amp; ListBox1.List(x) &amp; "]")
      End If
  Next x
  
  ActiveSheet.PivotTables("Pivottable1").PivotFields("[Range 1 1].[Quarter].[Quarter]").VisibleItemsList = myArray
  </code></pre>
  
  <p>I tested other ways which didn't work like using:</p>
  
  <pre><code>' This didn't work:
  Set myArray = CreateObject("System.Collections.ArrayList")
  myArray.Add "[Range 1 1].[Quarter].&amp;[1Q20]"
  
  ' This method didn't work either
  Dim myArray(2) As Variant
  myArray2(0) = "1Q20"
  myArray2(1) = "2Q20"
  </code></pre>
  
  <p>I need to replace <code>Array("[Range 1 1].[Quarter].&amp;[1Q20]", "[Range 1 1].[Quarter].&amp;[3Q20]")</code> with something I can set up dynamically. Any ideas on how I can do this?</p>