|
http://www.excelpx.com/thread-424101-1-1.html
以前的附件吧?下面通过修改录制宏改的
'添加数据透视表,通过数据透视表向导
Sub AddPt()
Dim pt As PivotTable
Dim x As Range, y As Range
Set x = Sheets(1).[a1].CurrentRegion
Set y = Sheets(2).[a1]
Call DelPt(y.Parent)
Set pt = y.Parent.Parent.PivotTableWizard(xlDatabase, x, y)
With pt
'1)拖放字段
.PivotFields("商品代码").Orientation = xlRowField
.PivotFields("品名").Orientation = xlRowField
.PivotFields("客户名称").Orientation = xlRowField
With .PivotFields("数量")
.Orientation = xlDataField
.Function = xlSum
End With
'2)其它设置
.PivotFields("商品代码").Subtotals(1) = False '不显示分类汇总
.PivotFields("品名").Subtotals(1) = False
.PivotFields("客户名称").Subtotals(1) = False
.ColumnGrand = False '总计 , 对行和列禁用
.RowGrand = False
.RepeatAllLabels xlRepeatLabels '重复所有项目标签
End With
Range("a1").CurrentRegion.EntireColumn.AutoFit
End Sub
'删除数据透视表
Sub DelPt(x As Worksheet)
Dim pt As PivotTable
Application.ScreenUpdating = False
x.Cells.Clear
For Each pt In x.PivotTables
pt.TableRange2.Clear '包括整个数据透视表(含页字段)的区域
Next
End Sub
1.rar
(16.09 KB, 下载次数: 12)
|
|