[转载]VBA创建数据透视表

Sub 透视表()Dim PTC As PivotCacheDim PVT As PivotTableDim rng As RangeSet 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 WithSet PTC = NothingSet PVT = NothingEnd Sub

原文链接：VBA创建数据透视表