|
'删除数据透视表
Sub DelPt(rng As Range)
Dim pt As PivotTable
For Each pt In rng.Parent.PivotTables
pt.TableRange2.Delete
Next
End Sub
Sub AddPt()
Dim pc As PivotCache
Dim pt As PivotTable
Dim sou As Range, des As Range
'
Set sou = Sheets(1).[a1].CurrentRegion
Set des = Sheets(2).[a1]
Call DelPt(des)
'1)数据透视表缓存对象
Set pc = ThisWorkbook.PivotCaches.Create(xlDatabase, sou, xlPivotTableVersion14)
'2)数据透视表对象
Set pt = pc.CreatePivotTable(des)
With pt
.PivotFields("商品代码").Orientation = xlRowField '行字段
.PivotFields("品名").Orientation = xlRowField
.PivotFields("客户名称").Orientation = xlColumnField '列字段
.PivotFields("数量").Orientation = xlDataField '数据字段
.PivotFields("商品代码").Subtotals(1) = False '隐藏分类汇总
End With
End Sub
Sub AddPt2()
Dim sh As Worksheet
Dim pt As PivotTable
Dim sou As Range, des As Range
'
Set sou = Sheets(1).[a1].CurrentRegion
Set des = Sheets(2).[a1]
Call DelPt(des)
'1)数据透视表对象
Set pt = des.Parent.Parent.PivotTableWizard(xlDatabase, sou, des)
With pt
.PivotFields("商品代码").Orientation = xlRowField '行字段
.PivotFields("品名").Orientation = xlRowField
.PivotFields("客户名称").Orientation = xlColumnField '列字段
.PivotFields("数量").Orientation = xlDataField '数据字段
.PivotFields("商品代码").Subtotals(1) = False '隐藏分类汇总
End With
End Sub
1.rar
(20.65 KB, 下载次数: 12)
如题,请问需要怎样修改?谢谢
|
|