title: Excel Vba, create pivot table, autoshow by value top 10, error 1004
body: <p>I'm trying to create a top 10 value with the pivot table created with excel vba but it seems like I'm getting an error</p>
  
  <blockquote>
    <p>Run-time Error '1004':</p>
    
    <p>Application-defined or object defined error</p>
  </blockquote>
  
  <p>Here is the code, I can't seems to find the problem after I search through the internet</p>
  
  <pre><code>Sub PivotGenerate()
  
  'declaration
  Dim sht As Worksheet
  Dim pvt As PivotTable
  Dim pvtFld As PivotField
  Dim pvtCache As PivotCache
  Dim StartPvt As String
  Dim SrcData As String
  Dim RangeInput As Range
  
  'Input value
  Set RangeInput = Application.InputBox(Prompt:= _
          "Please Select Name range", _
          Title:="InputBox Method", Type:=8)
  
  'Stop Updating
  Application.ScreenUpdating = False
  
  'data range for pivot
  SrcData = ActiveSheet.Name &amp; "!" &amp; RangeInput.Address(ReferenceStyle:=xlR1C1)
  
  'error handling for new worksheet
  Application.DisplayAlerts = False
  
  On Error Resume Next
  ThisWorkbook.Sheets("PivotTable").Delete
  On Error GoTo 0
  Application.DisplayAlerts = True
  
  'create new worksheet
  Set sht = Worksheets.Add(After:=Sheets(Worksheets.Count))
  sht.Name = "PivotTable"
  
  'Pivot Table location
  StartPvt = sht.Name &amp; "!" &amp; sht.Range("A3").Address(ReferenceStyle:=xlR1C1)
  
  'Create Pivot Cache from Source Data
  Set pvtCache = ActiveWorkbook.PivotCaches.Create( _
      SourceType:=xlDatabase, _
      SourceData:=SrcData)
  
  'Create Pivot table from Pivot Cache
  Set pvt = pvtCache.CreatePivotTable(TableDestination:=StartPvt, TableName:="Top 10 Commitment Value")
  
  'Set active
  Set pvt = ActiveSheet.PivotTables("Top 10 Commitment Value")
  
  'Classic View
  pvt.InGridDropZones = True
  pvt.RowAxisLayout xlTabularRow
  
  'Remove Subtotal
  With pvt
     For Each pvtFld In .PivotFields
     pvtFld.Subtotals(1) = False
  Next pvtFld
  End With
  
  'Row Labels
  pvt.PivotFields("CUST Name").Orientation = xlRowField
  pvt.PivotFields("CUST Name").Position = 1
  
  'Data Value
  pvt.AddDataField pvt.PivotFields("Commitment RM"), "Sum of Commitment RM", xlSum
  pvt.AddDataField pvt.PivotFields("OS BALANCE RM (On BS)"), "Sum of OS BALANCE RM (On BS)", xlSum
  
  pvt.PivotFields("CUST Name").AutoShow Type:=xlAutomatic, Range:=xlTop, Count:=5, Field:="Sum of Commitment RM"
  
  
  'Activate Updating
  Application.ScreenUpdating = True
  
  End Sub
  </code></pre>
  
  <p>The debugging highlighted this line in the code</p>
  
  <blockquote>
    <p>pvt.PivotFields("CUST Name").AutoShow Type:=xlAutomatic, Range:=xlTop, Count:=5, Field:="Sum of Commitment RM"</p>
  </blockquote>
  
  <p>The Pivot Table is manage to be generated but the Top 10 line gave error</p>