'http://www.excelpx.com/thread-424101-1-1.html
'创建方式1:通过数据透视表向导。03可用
Sub CreatePivotTable1()
Dim pt As PivotTable
Dim data As Range
Dim des As Range
'1)相关
Set data = Sheets(1).[a1].CurrentRegion '数据源
Set des = Sheets(2).[a1] '透视表的位置
des.CurrentRegion.Clear
'2)创建数据透视表对象
Set pt = des.Parent.PivotTableWizard(xlDatabase, data, des)
'3)设置数据透视表
With pt
'行字段
.PivotFields("商品代码").Orientation = xlRowField
.PivotFields("品名").Orientation = xlRowField
'列字段
.PivotFields("客户名称").Orientation = xlColumnField
'数值字段
.PivotFields("数量").Orientation = xlDataField
'商品代码字段有分类汇总
.PivotFields("商品代码").Subtotals(1) = False
End With
End Sub