title: Pivots and Run-time error 1004: Application-defined or object-defined error
body: <p>I'm quite new to macros and VBA coding and I'm trying to create a really simple macro that takes the data from a table with 33 columns and convert it to a pivot.</p>
  
  <p>Only the last 3 columns (31, 32, 33) contains numbers and I need that to appear on the pivot as I want to compare current month, last month and the movement from month on month.</p>
  
  <p>Here is my code so far:</p>
  
  <pre><code>Sub Macro6()
  '
  ' Macro6 Macro
  '
  
  '
      Dim shtSrc As Worksheet, shtDest As Worksheet
      Dim pc As PivotCache
  
      Set shtSrc = ActiveSheet
  
      Set shtDest = shtSrc.Parent.Sheets.Add()
      shtDest.Name = shtSrc.Name &amp; "-Pivot"
  
      Set pc = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
          SourceData:=shtSrc.Range("A1").CurrentRegion)
      pc.CreatePivotTable TableDestination:=shtDest.Range("A3"), _
          TableName:="PivotTable1"
  
      With shtDest.PivotTables("PivotTable1")
          .InGridDropZones = True
          .RowAxisLayout xlTabularRow
      End With
  
      Set shtDest = ActiveSheet
  
      With ActiveSheet.PivotTables("PivotTable1").PivotFields("Name")
          .Orientation = xlRowField
          .Position = 1
      End With
  
      ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
          "PivotTable1").PivotFields(31), "Last month", xlSum
      ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
          "PivotTable1").PivotFields(32), "This month", xlSum
      ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
          "PivotTable1").PivotFields(33), "Movement", xlSum
  
  
  
  End Sub
  </code></pre>
  
  <p>I get an error Run-time error 1004: Application-defined or object-defined error on this line:</p>
  
  <pre><code>ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
      "PivotTable1").PivotFields(33), "Movement", xlSum
  </code></pre>
  
  <p>If I take away this line, the macro works fine but only creates 2 column. If I put in the line, it generates the error and does not create the 3rd column.</p>
  
  <p>Can anyone explain why?</p>