'创建方式1:先创建数据透视表缓存对象,再创建数据透视表
Sub test1()
Dim pc As PivotCache
Dim pt As PivotTable
'创建空白工作表,存放数据透视表。
Sheets.Add after:=Sheets(Sheets.Count)
'创建 数据透视表的缓存(PivotCache 对象)
Set pc = ThisWorkbook.PivotCaches.Create(xlDatabase, Sheets("Sheet1").[a1].CurrentRegion, 2)
'创建一个基于 数据透视表的缓存(PivotCache 对象)的数据透视表。透视表左上角在A1
Set pt = pc.CreatePivotTable([a1])
With pt
.PivotFields("单价").Orientation = xlRowField
.PivotFields("数量").Orientation = xlDataField
.PivotFields("单价").Orientation = xlDataField
.PivotFields("金额").Orientation = xlDataField
.DataPivotField.Orientation = xlColumnField
.PivotFields("求和项:单价").Caption = "最大值项:单价"
.PivotFields("最大值项:单价").Function = xlMax
End With
End Sub