title: Pivot Table Formatting returns error
body: <p>I am working on a macro in which I am putting together PivotTables. </p>
  
  <p>My Problem:
  I can not get the calculation part to work. I get an error everytime I run the code at the stage where PivotField(18) is supposed to calculate the .Function=xlCount. The error I get is "1004 select method of range class failed".</p>
  
  <p>All the examples I've found online and in the MS Help section are structured the way I have set up my code. What am I missing???</p>
  
  <p>Any help is highly appreciated... </p>
  
  <p>I've been working on this the better part of the day. The following page (<a href="http://peltiertech.com/WordPress/referencing-pivot-table-ranges-in-vba/" rel="nofollow noreferrer">Link</a>) has been super helpful, but hasn't helped me in cracking this particular problem.</p>
  
  <pre><code>' Start of PivotSpecific Formatting
  Set pt = ActiveSheet.PivotTables("PT" &amp; wsNewWorksheetName)
  With pt
      ' Prevent AutoFormatting of Columns
      .HasAutoFormat = False
      ' Hide Field Headers
      .DisplayFieldCaptions = False
      .TableStyle2 = "PivotStyleMedium2"
      ' Apply calculation to numbers and change Caption
      With .PivotFields(18)
          .Caption = "Demand"
          .Function = xlCount
          .NumberFormat = "#,##0"
      End With
      With .PivotFields(15)
          .Caption = "Lowest OP"
          .Function = xlMin
          .NumberFormat = "#,##0.00"
      End With
  End With
  </code></pre>
  
  <p>Update 1: Screenshot of Pivot Table
  <img src="https://i.stack.imgur.com/YuFwL.png" alt="Pivot Table Screenshot"></p>
  
  <p>Update 2: Tested Code from bonCodigo
  Here is the code adjusted to my workbook. With this I get a run time error 91 on the line:
  Set pvField = pvTable.PivotFields(18)</p>
  
  <pre><code>Dim wkSheet As Worksheet
  Dim pvTable As PivotTable
  Dim pvField As PivotField
  
  Set wkSheet = ThisWorkbook.Worksheets(wsNewWorksheetName)
  Set pvTble = wkSheet.PivotTables("PT" &amp; wsNewWorksheetName)
  Set pvField = pvTable.PivotFields(18)
  
  With pvField
       .Caption = "Demand"
       .Function = xlCount
       .orientation = xlDataField
       .NumberFormat = "#,##0"
  End With
  
  Set pvField = Nothing
  Set pvTable = Nothing
  Set wkSheet = Nothing
  </code></pre>