title: Creating Pivot Table with Distinct Count using VBA
body: <p>I am trying to create a pivot table using Excel 2013 VBA with DISTINCT COUNT as a value field. </p>
  
  <p>I understand that if you create the pivot table manually, you have to check the "Add this data to the Data Model" checkbox in order to have the distinct count option for the value pivotfield, but I have no idea how to translate this into VBA code.</p>
  
  <p>I've tried creating the pivot table with xlCount as the valuue pivotfield and it worked fine, but for xlDistinctCount it does not work</p>
  
  <pre><code>Set wb = ActiveWorkbook
  Set ws = wb.Sheets.Add(Type:=xlWorksheet, After:=Application.Worksheets(1))
  Worksheets(1).Range("A1:I" &amp; i).Copy
  Worksheets(2).Range("A1").PasteSpecial xlPasteValues
  Worksheets(2).Name = "PivotTable"
  
  'Defining data range for pivottable
  lastrow = Worksheets("PivotTable").Cells(Rows.Count, 1).End(xlUp).Row
  lastCol = Worksheets("PivotTable").Cells(1, Columns.Count).End(xlToLeft).column
  Set pRange = Worksheets("PivotTable").Cells(1, 1).Resize(lastrow, lastCol)
  
  
  On Error Resume Next
  'Define Pivot Cache
  Set PCache = ActiveWorkbook.PivotCaches.create _
  (SourceType:=xlDatabase, SourceData:=pRange, Version:=xlPivotTableVersion12). _
  CreatePivotTable(TableDestination:=Worksheets("PivotTable").Cells(2, 10), _
  TableName:="SalesPivotTable")
  
  
  Set PTable = PCache.CreatePivotTable _
  (TableDestination:=Worksheets("PivotTable").Cells(2, 10), TableName:="SalesPivotTable")
  
  With Worksheets("PivotTable").PivotTables("SalesPivotTable").PivotFields("User")
      .Orientation = xlRowField
      .Position = 1
  End With
  
  With Worksheets("PivotTable").PivotTables("SalesPivotTable").PivotFields("BinType")
      .Orientation = xlColumnField
      .Position = 1
  End With
  
  
  'Doesn't work with xlDistinctCount but does with xlCount
  With Worksheets("PivotTable").PivotTables("SalesPivotTable")
      .AddDataField Worksheets("PivotTable").PivotTables( _
          "SalesPivotTable").PivotFields("AppNo"), "Distinct Count of AppNo", 
           xlDistinctCount
  End With
  </code></pre>
  
  <p>I expect the pivot table to update with the distinct count once the last line is computed like it does with xlCount but it just doesn't do anything with xlDistinctCount</p>