title: Loop Adding Calculated Fields to Multiple Pivot Tables
body: <p>I'm trying to add two calced fields into 24 pivot tables across 8 worksheets. It it based on choice of a data validation list in B4 on Sheet 1.</p>
  
  <p>The code to remove the current two calced fields is working:</p>
  
  <pre><code>Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
  
  Dim sht As Worksheet
  Dim pt As PivotTable
  Dim pf As PivotField
  
  On Error Resume Next
  If Intersect(Target, Sheet1.Range("B4")) Is Nothing Then Exit Sub
  
  For Each sht In ThisWorkbook.Worksheets
      For Each pt In sht.PivotTables
          For Each pf In pt.DataFields
              pf.DataRange.Cells(1, 1).PivotItem.Visible = False
          Next pf
      Next pt
  Next sht
  </code></pre>
  
  <p>Next, I need to add two new calculated fields which I can't get to work:</p>
  
  <pre><code>If Sheet1.Range("B4") = "Hourly Utilization" Then
      For Each sht In ThisWorkbook.Worksheets
          For Each pt In sht.PivotTables
              With pt
                  .AddDataField sht.PivotTables( _
                  "*").PivotFields("hClientUtil"), "Client"
                  .DataBodyRange.NumberFormat = "#0.0%"
  
                  .AddDataField sht.PivotTables( _
                  "*").PivotFields("hProdUtil"), "Productive"
                  .DataBodyRange.NumberFormat = "#0.0%"
              End With
          Next pt
      Next sht
  End If
  </code></pre>
  
  <p>I can get it working if a specify the pivot table by sheet and name but I'd like to be able to loop it instead of have the code repeated 24 times.</p>
  
  <p>I'm thinking it maybe something with the "*" as I dont know how to specify the pivot table without using the pivot table name. Any ideas?</p>