title: VBA - Loop Each Item in Pivot Filter and Paste into new sheet
body: <p>I have a challenge... I have a range in Sheet Lookup with each possible value in Pivot table filter "Owner: Full Name".</p>
  
  <p>The range with the names are Sheets "Lookup" Range B2:B98. (Problem 1: This range can change as it creates this list in a different code, how to set this to a dynamic range?)</p>
  
  <p>Once it filters on that i.e. value in B2 it should copy this filtered pivot into a new sheet and name the sheet after the value in b2.</p>
  
  <p>Then it should "deselect" the b2 item and go to filter on value in b3 and continue.</p>
  
  <p>Problem 2: Setting the filter correctly to loop and filter on each single value in the new dynamic lookup range.</p>
  
  <p>Here is what I have at the moment...</p>
  
  <pre><code>Option Explicit
  
      Dim wb As Workbook, ws, ws1, ws2 As Worksheet, PT As PivotTable, PTI As 
      PivotItem, PTF As PivotField, rng As Range
  
      Sub Filter_Pivot()
  
      Set wb = ThisWorkbook
      Set ws = wb.Sheets("Copy")
      Set ws1 = wb.Sheets("Lookup")
      Set PT = ws.PivotTables("PivotCopy")
      Set PTF = PT.PivotFields("Owner: Full Name")
  
  
          For Each rng In ws1.Range("B2:B98")
              With PTF
                  .ClearAllFilters
                  For Each PTI In PTF.PivotItems
                      PTI.Visible = (PTI.Name = rng)
                  Next PTI
              Set ws2 = Sheets.Add
                  ws1.Name = PTI
                  .TableRange2.Copy
                  ws2.Range("A1").PasteSpecial
              End With
          Next rng
  
  
      End Sub
  </code></pre>