VBA创建数据透视表

Sub 透视表()
Dim PTC As PivotCache
Dim PVT As PivotTable
Dim rng As Range
Set rng = Sheet1.Range("a1:d45") '数据源
Set PTC = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=rng, Version:=xlPivotTableVersion14)
Set PVT = PTC.CreatePivotTable(TableDestination:=Range("h1"), TableName:="透视试验", DefaultVersion:=xlPivotTableVersion14)
Set rng = Nothing '释放对象变量
With PVT

.AddFields RowFields:=Array("编码", "名称"), ColumnFields:="方向" '填加行字段和列字段
.AddDataField .PivotFields("本币"), "计数:本币", xlCount '计数
.AddDataField .PivotFields("本币"), "sum 本币", xlSum '求和
'筛选
' For Each bm In .PivotFields("编码").PivotItems
' If bm <> "05-0018" Then
' bm.Visible = False
' End If
' Next
.RowAxisLayout xlTabularRow '以表格形式显示
.ShowDrillIndicators = False '取消+-按钮
.RowGrand = False '每行汇总不显示
.ColumnGrand = False '每列汇总不显示
.PivotFields("编码").Subtotals(1) = False ''去掉编码字段中分类汇总功能,Subtotals(1)=false代表将索引 1(自动)为 false,则其他所有值将设置为 False。如果需要分类汇总功能,则设置为true或不要这句;
End With
Set PTC = Nothing
Set PVT = Nothing
End Sub

posted @ 2019-10-18 00:22  zhujie-  阅读(2211)  评论(0编辑  收藏  举报