title: Creating multiple pivot tables using Excel VBA - each pivot table filtered with a distinct value
body: <p>I have done some searching and can't find any posts with similar issue. I want to create 3 pivot tables on a new sheet, with each table filtered by column B as shown below. The number of items in column B may increase/decrease. I need the macro to loop through all the different combinations of items and apply this filter to each pivot table created.</p>
  <p>I have very limited experience with loops in VBA so don't even know where to start.</p>
  <p>I have attached screenshots below of the required solution + the code VBA generates for creating a pivot table from a sample table I've created.</p>
  <pre><code>Sub Macro1()
  '
  ' Macro1 Macro
  '
  ' Keyboard Shortcut: Ctrl+a
  '
      Range(&quot;A1&quot;).Select
      Application.CutCopyMode = False
      ActiveSheet.ListObjects.Add(xlSrcRange, Range(&quot;$A$1:$C$5&quot;), , xlYes).Name = _
          &quot;Table1&quot;
      Range(&quot;Table1[#All]&quot;).Select
      ActiveSheet.ListObjects(&quot;Table1&quot;).TableStyle = &quot;TableStyleLight1&quot;
      Application.CutCopyMode = False
      Sheets.Add
      ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
          &quot;Table1&quot;, Version:=7).CreatePivotTable TableDestination:=&quot;Sheet2!R3C1&quot;, _
          TableName:=&quot;PivotTable1&quot;, DefaultVersion:=7
      Sheets(&quot;Sheet2&quot;).Select
      Cells(3, 1).Select
      With ActiveSheet.PivotTables(&quot;PivotTable1&quot;)
          .ColumnGrand = True
          .HasAutoFormat = True
          .DisplayErrorString = False
          .DisplayNullString = True
          .EnableDrilldown = True
          .ErrorString = &quot;&quot;
          .MergeLabels = False
          .NullString = &quot;&quot;
          .PageFieldOrder = 2
          .PageFieldWrapCount = 0
          .PreserveFormatting = True
          .RowGrand = True
          .SaveData = True
          .PrintTitles = False
          .RepeatItemsOnEachPrintedPage = True
          .TotalsAnnotation = False
          .CompactRowIndent = 1
          .InGridDropZones = False
          .DisplayFieldCaptions = True
          .DisplayMemberPropertyTooltips = False
          .DisplayContextTooltips = True
          .ShowDrillIndicators = True
          .PrintDrillIndicators = False
          .AllowMultipleFilters = False
          .SortUsingCustomLists = True
          .FieldListSortAscending = False
          .ShowValuesRow = False
          .CalculatedMembersInFilters = False
          .RowAxisLayout xlCompactRow
      End With
      With ActiveSheet.PivotTables(&quot;PivotTable1&quot;).PivotCache
          .RefreshOnFileOpen = False
          .MissingItemsLimit = xlMissingItemsDefault
      End With
      ActiveSheet.PivotTables(&quot;PivotTable1&quot;).RepeatAllLabels xlRepeatLabels
      With ActiveSheet.PivotTables(&quot;PivotTable1&quot;).PivotFields(&quot;Category&quot;)
          .Orientation = xlRowField
          .Position = 1
      End With
      ActiveSheet.PivotTables(&quot;PivotTable1&quot;).AddDataField ActiveSheet.PivotTables( _
          &quot;PivotTable1&quot;).PivotFields(&quot;Traffic&quot;), &quot;Sum of Traffic&quot;, xlSum
      With ActiveSheet.PivotTables(&quot;PivotTable1&quot;).PivotFields( _
          &quot;Assign a unique number to a value&quot;)
          .Orientation = xlPageField
          .Position = 1
      End With
      ActiveSheet.PivotTables(&quot;PivotTable1&quot;).PivotFields( _
          &quot;Assign a unique number to a value&quot;).ClearAllFilters
      ActiveSheet.PivotTables(&quot;PivotTable1&quot;).PivotFields( _
          &quot;Assign a unique number to a value&quot;).CurrentPage = &quot;1&quot;
  End Sub 
  </code></pre>
  <p>Also enclosing the sample data screenshot. Thanks in advance guys!</p>
  <p>EDIT: Pictures didn't upload so trying to upload again now.
  <a href="https://i.stack.imgur.com/ithA6.png" rel="nofollow noreferrer">macro_test_data</a></p>
  <p><a href="https://i.stack.imgur.com/fwgcD.png" rel="nofollow noreferrer">macro_test_pivot_table</a></p>