Excel用VBA创建数据透视表

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.

我正在用VBA创建一个简单的Pivot表。我不知道我的代码有什么问题。我的代码运行无误，但结果不同。你能告诉我我代码中的问题在哪里吗?

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.

我的代码给出了这个结果。这是错误的。

2 个解决方案

#1

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

我认为您的问题是在最初的小范围内，只有两列(A, B)和最后几行代码

'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)

假设你的名字在A列，调用模块，B列是KW和一个数字(模块的KW/h)

'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.

你不需要最后一行。

#2

Try adding the xlRowField and xlColumnField after you added the calculated field.

在添加计算字段之后，尝试添加xlRowField和xlColumnField。

e.g.

如。

first

第一个

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

then

然后

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

#1