Excel creates VBA PivotTable

Question

I am creating a simple VBA pivot table. I do not know what is wrong with my code. My code works without errors, but the results are different. Could you tell me where the problem is 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 that I get, and this is what I want in the end.

My code gives me this result. It is not right.

Answer 1

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

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

Suppose you have your name in column A, call the module, and column B - KW with the number (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 do not need the last line.

Answer 2

Try adding xlRowField and xlColumnField after by adding the calculated field.

eg.

the first

 pvt.AddDataField pvt.PivotFields("Module"), "Anzahl von Module", xlCount 

then

 pvt.PivotFields("Module").Orientation = xlRowField pvt.PivotFields("KW").Orientation = xlColumnField