title: Formatting Pivot Table (.NumberFormat)
body: <p><strong>OBJECTIVE</strong></p>
  
  <p>Pull in a normalized dataset. Place dataset into a pivot table.</p>
  
  <p><strong>APPROACH</strong></p>
  
  <ol>
  <li>Normalize Data</li>
  <li>Select normalized data and create pivot table</li>
  <li>Configure pivot table headings and columns</li>
  <li>Format headings and columns</li>
  </ol>
  
  <p><strong>CODE</strong></p>
  
  <pre><code>Sub createPivot()
  
  Dim ws As Worksheet
  Dim pvtCache As pivotCache
  Dim pvt As pivotTable
  Dim srcData As String
  Dim lastRow As Long
  Dim startPvt As String
  Dim target As Worksheet
  
  'Delete previous pivottable
  Worksheets("PIVOT").PivotTables("PivotTable1").TableRange2.Clear
  
  'Select pivot table data
  Worksheets("CONSOLIDATED").Activate
  Set ws = ActiveSheet
  lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
  srcData = ActiveSheet.Name &amp; "!" &amp; Range("A1:H" &amp; lastRow).Address(ReferenceStyle:=xlR1C1)
  
  'Set pivot table location
  Set target = ThisWorkbook.Worksheets("PIVOT")
  startPvt = target.Name &amp; "!" &amp; target.Range("A1").Address(ReferenceStyle:=xlR1C1)
  
  'Create pivot cache
  Set pvtCache = ActiveWorkbook.PivotCaches.Create( _
      SourceType:=xlDatabase, _
      SourceData:=srcData)
  
  'Deploy pivot table
  Set pvt = pvtCache.CreatePivotTable( _
      TableDestination:=startPvt, _
      TableName:="PivotTable1")
  
      'Add Pivot Fields
      pvt.PivotFields("Fiscal Year").Orientation = xlColumnField
      pvt.PivotFields("Fiscal Year").Position = 1
  
      pvt.PivotFields("Fiscal Month").Orientation = xlColumnField
      pvt.PivotFields("Fiscal Month").Position = 2
  
      pvt.PivotFields("Unit").Orientation = xlRowField
      pvt.PivotFields("Unit").Position = 1
  
      pvt.PivotFields("Project").Orientation = xlRowField
      pvt.PivotFields("Project").Position = 2
  
      pvt.PivotFields("Base Expense").Orientation = xlDataField
  
      'Format cells
      pvt.PivotFields("Base Expense").NumberFormat = "$ #,##0"
  
  End Sub
  </code></pre>
  
  <p><strong>ISSUE</strong></p>
  
  <p>No errors, however <code>pvt.PivotFields("Base Expense").NumberFormat = "$ #,##0"</code> does not take effect.</p>