Excel create Pivot Table by VBA

Question

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.

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 & "!" & 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 & "!" & 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

If I do it manually, this is the result I get and this is what I want at the end.

My code gives me this result. Which is wrong.

Answer 1

I think your problem is in your small initial range with only two columns (A, B) and the last lines of code

'Determine the data range you want to pivot
  SrcData = ActiveSheet.Name & "!" & Range("A1:B53821").Address(ReferenceStyle:=xlR1C1)

Let's suppose you have your name in column A, call Module and column B is KW with a number (the KW/h of your module)

'Create Pivot table from Pivot Cache
Set pvt = pvtCache.CreatePivotTable(TableDestination:=StartPvt, TableName:="PivotTableTest")

pvt.AddDataField pvt.PivotFields("Module"), "Num Module", xlCount
pvt.PivotFields("Module").Orientation = xlColumnField ' not xlRowField
pvt.PivotFields("KW").Orientation = xlRowField ' not xlColumnField

You don't need the last line.