title: Excel create Pivot Table by VBA
body: <p>I am creating a simple Pivot table by VBA. I don't know what is wrong with my code. My code runs without error but the results is different. Could you please tell me where is the problem in my code. </p>
  
  <pre><code>Sub CreatePivotTable()
  
  Dim sht As Worksheet
  Dim pvtCache As PivotCache
  Dim pvt As PivotTable
  Dim StartPvt As String
  Dim SrcData As String
  
  'Determine the data range you want to pivot
    SrcData = ActiveSheet.Name &amp; "!" &amp; Range("A1:B53821").Address(ReferenceStyle:=xlR1C1)
  
  'Create a new worksheet
    Set sht = Sheets.Add
  
  'Where do you want Pivot Table to start?
    StartPvt = sht.Name &amp; "!" &amp; sht.Range("A1").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:="PivotTable1")
  
  
  pvt.PivotFields("Module").Orientation = xlRowField
  pvt.PivotFields("KW").Orientation = xlColumnField
  
  pvt.AddDataField pvt.PivotFields("Module"), "Anzahl von Module", xlCount
  
  pvt.PivotFields("Module").PivotFilters.Add Type:=xlTopCount, DataField:=pvt.PivotFields("Anzahl von Module"), Value1:=12
  
  End Sub
  </code></pre>
  
  <p>If I do it manually, this is the result I get and this is what I want at the end.</p>
  
  <p><a href="https://i.stack.imgur.com/UoK7V.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/UoK7V.jpg" alt="enter image description here"></a>  </p>
  
  <p>My code gives me this result. Which is wrong.</p>
  
  <p><a href="https://i.stack.imgur.com/Abjzz.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/Abjzz.jpg" alt="enter image description here"></a></p>