title: Code Previously used to remove subtotal from a pivot table in excel using vba not working now
body: <p>I have been using a vba code to create pivot tables and remove grand &amp; sub-totals for a couple of weeks now. The subtotal part of the code which was previously working well is not throwing the following error</p>
  
  <pre><code>Run-Time Error '1004': Unable to set the Subtotals property of the PivotField Class
  </code></pre>
  
  <p>and i an not to figure whats causing it.</p>
  
  <p>Below is the code, Appreciate any help.</p>
  
  <p>Regards</p>
  
  <pre><code>Sub Pivottable_5()
  
      Dim objTable As PivotTable
      Dim objField As PivotField
  
      ' Select the sheet and first cell of the table that contains the data.
      ActiveWorkbook.Sheets("All Proj").Select
      Range("A1").Select
  
      ' Create the PivotTable object based on the Employee data on Sheet1.
      Set objTable = ActiveSheet.PivotTableWizard
  
      ' Specify row and column fields.
      Set objField = objTable.PivotFields("Products")
      objField.Orientation = xlRowField
      Set objField = objTable.PivotFields("Product")
      objField.Orientation = xlRowField
      Set objField = objTable.PivotFields("Value Category")
      objField.Orientation = xlRowField
      objField.PivotItems("(blank)").Visible = False
  
      Set objField = objTable.PivotFields("Driven?")
      objField.Orientation = xlPageField
      objField.PivotItems("No").Visible = False
      objField.PivotItems("(blank)").Visible = False
      objField.PivotItems("Not Classified").Visible = False
  
      Set objField = objTable.PivotFields("Project Status")
      objField.Orientation = xlPageField
      objField.PivotItems("Cancelled").Visible = False
      objField.PivotItems("Suspended").Visible = False
      objField.PivotItems("Scoped not active").Visible = False
      objField.PivotItems("(blank)").Visible = False
  
      Set objField = objTable.PivotFields("SPA Business Unit")
      objField.Orientation = xlPageField
      objField.PivotItems("-").Visible = False
      objField.PivotItems("Not Classified").Visible = False
      objField.PivotItems("Plth").Visible = False
      objField.PivotItems("(blank)").Visible = False
  
      ' Specify a data field with its summary
      ' function and format.
      Set objField = objTable.PivotFields("2014 Actual Total")
      objField.Orientation = xlDataField
      objField.Function = xlSum
      objField.NumberFormat = "$ #,##0"
  
      Set objField = objTable.PivotFields("2015 Actual Total")
      objField.Orientation = xlDataField
      objField.Function = xlSum
      objField.NumberFormat = "$ #,##0"
  
      Set objField = objTable.PivotFields("2016 Actual Total")
      objField.Orientation = xlDataField
      objField.Function = xlSum
      objField.NumberFormat = "$ #,##0"
  
      Set objField = objTable.PivotFields("2016 Forecast Total")
      objField.Orientation = xlDataField
      objField.Function = xlSum
      objField.NumberFormat = "$ #,##0"
  
      Set objField = objTable.PivotFields("2017 Forecast Total")
      objField.Orientation = xlDataField
      objField.Function = xlSum
      objField.NumberFormat = "$ #,##0"
  
      Set objField = objTable.PivotFields("2018 Forecast Total")
      objField.Orientation = xlDataField
      objField.Function = xlSum
      objField.NumberFormat = "$ #,##0"
  
      Set objField = objTable.PivotFields("2019 Forecast Total")
      objField.Orientation = xlDataField
      objField.Function = xlSum
      objField.NumberFormat = "$ #,##0"
  
      ActiveSheet.name = "iNexus Pivot"
  
      ' Rename the pivot table
      With Sheets("iNexus Pivot")
          .PivotTables(1).name = "PivotTable2"
      End With
  
      With Sheets("iNexus Pivot").PivotTables("PivotTable2").DataPivotField
          .Orientation = xlColumnField
      End With
  
      ActiveSheet.PivotTables("PivotTable2").ColumnGrand = False
      ActiveSheet.PivotTables("PivotTable2").RowGrand = False
  
      Dim i As Integer
      Dim iFieldMax As Integer
  
       'Find the number of PivotFields
      iFieldMax = ActiveSheet.PivotTables("PivotTable2").PivotFields.Count
  
       'Loop through the fields in the Pivot
      For i = 1 To iFieldMax
  
          With ActiveSheet.PivotTables("PivotTable2").PivotFields(i)
  
               'Set subtotal calculation to nothing
              .Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
  
          End With
  
      Next i
  
      ActiveSheet.PivotTables("PivotTable2").RowAxisLayout xlTabularRow
  
      ActiveWindow.Zoom = 80
  
      ActiveSheet.Cells.EntireColumn.AutoFit
  
      ActiveSheet.Cells(ActiveCell.Row, 1).Select
  
      ' Move the cursor to the sheet with the buttons
      Application.Goto Reference:="Sheet1!R1C1"
  
      ActiveWorkbook.Save
  
  End Sub
  </code></pre>