VBA实战——创建数据透视表

1、v1.0可编辑可修改3Sub创建数据透视表()Name:="database1", RefersToR1C1:= _"=OFFSET(R4c3,COUNTA(C3),COUNTA(R4)”("database1").Comment =""Rows("5:5").SelectShift:=xlUpRange("C4").SelectSourceData:= _"databasel", Version:=xlPivotTableVersion14).CreatePivo

2、tTable _TableDestinationk"Sheet1!R3C1”,DefaultVersion:= _xlPivotTableVersion14Sheets("Sheet1").SelectCells(3, 1).SelectWith ("数据透视表 1").PivotFields(".Orientation = xlPageField.Position = 1End WithWith ("数据透视表 1").PivotFields(".Orientation = xlRowField.Pos

3、ition = 1End WithWith ("数据透视表 1").PivotFields(".Orientation = xlRowFieldTableName:=" 数据透视表 1",指令单单号”)名称1")作业").Position = 2End WithWith ("数据透视表 1").PivotFields(".Orientation = xlRowField.Position = 3End WithWith ("数据透视表 1").PivotFields(&quo

4、t;.Orientation = xlRowField.Position = 4End WithWith ("数据透视表 1").PivotFields(".Orientation = xlRowField.Position = 5End With("数据透视表 1").AddDataField (").PivotFields("需求量")，"Range("B21").SelectWith ("数据透视表1").InGridDropZones = True.RowA

5、xisLayout xlTabularRowEnd WithRange("D6").Select("数据透视表 1").PivotFields("False, False, False, False,Range("C6").Select("数据透视表 1").PivotFields("False, False, False, False,物料")物料描述")供应商")数据透视表1" _求和项:需求量", xlSum物料描述"Subtot

6、als = Array(False, _False,False,False,False,False,False物料".Subtotals = Array(False, _False,False,False,False,False,FalseFalse)False)Range("B6").Selectv1.0可编辑可修改("数据透视表 1").PivotFields("作业").Subtotals = Array(False, _False, False, False, False, False, False, False,

7、False, False, False, False)Range("A7").Select("数据透视表 1").PivotFields(" 名称 1").Subtotals = Array(False, _False, False, False, False, False, False, False, False, False, False, False)("数据透视表 1").PivotSelect"", xlDataAndLabel, True(xlDiagonalDown).LineSt

8、yle = xlNone(xlDiagonalUp).LineStyle = xlNoneWith (xlEdgeLeft).LineStyle = xlContinuous.ColorIndex = 0.TintAndShade = 0.Weight = xlThinEnd WithWith (xlEdgeTop).LineStyle = xlContinuous.ColorIndex = 0.TintAndShade = 0.Weight = xlThinEnd WithWith (xlEdgeBottom).LineStyle = xlContinuous.ColorIndex = 0.

9、TintAndShade = 0.Weight = xlThinEnd WithWith (xlEdgeRight).LineStyle = xlContinuous.Colorindex = 0.TintAndShade = 0.Weight = xlThinEnd WithWith (xllnsideVertical).LineStyle = xlContinuous.Colorindex = 0.TintAndShade = 0.Weight = xlThinEnd WithWith (xllnsideHorizontal).LineStyle = xlContinuous.Colori

10、ndex = 0.TintAndShade = 0.Weight = xlThinEnd WithRange("A13").Select("数据透视表 1").ShowDrillIndicators = FalseColumns("A:A").ColumnWidth =Columns("B:F").SelectWith Selection.HorizontalAlignment = xlLeft.VerticalAlignment = xlCenter.WrapText = False.AddIndent = False.IndentLevel = 0.ShrinkToFit = False.ReadingOrder = xlContext.MergeCells = FalseEnd WithColumns("B:F").("数据透视表 1").PivotSelect"物料描述All”, xlLabelOnly, TrueColumns("F:F").SelectWith Selection.HorizontalAlignment = xlCenter.Vert