VBA實戰(zhàn)——創(chuàng)建數(shù)據(jù)透視表

1、v1.0可編輯可修改3Sub創(chuàng)建數(shù)據(jù)透視表()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 ("數(shù)據(jù)透視表 1").PivotFields(".Orientation = xlPageField.Position = 1End WithWith ("數(shù)據(jù)透視表 1").PivotFields(".Orientation = xlRowField.Pos

3、ition = 1End WithWith ("數(shù)據(jù)透視表 1").PivotFields(".Orientation = xlRowFieldTableName:=" 數(shù)據(jù)透視表 1",指令單單號”)名稱1")作業(yè)").Position = 2End WithWith ("數(shù)據(jù)透視表 1").PivotFields(".Orientation = xlRowField.Position = 3End WithWith ("數(shù)據(jù)透視表 1").PivotFields(&quo

4、t;.Orientation = xlRowField.Position = 4End WithWith ("數(shù)據(jù)透視表 1").PivotFields(".Orientation = xlRowField.Position = 5End With("數(shù)據(jù)透視表 1").AddDataField (").PivotFields("需求量")，"Range("B21").SelectWith ("數(shù)據(jù)透視表1").InGridDropZones = True.RowA

5、xisLayout xlTabularRowEnd WithRange("D6").Select("數(shù)據(jù)透視表 1").PivotFields("False, False, False, False,Range("C6").Select("數(shù)據(jù)透視表 1").PivotFields("False, False, False, False,物料")物料描述")供應商")數(shù)據(jù)透視表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可編輯可修改("數(shù)據(jù)透視表 1").PivotFields("作業(yè)").Subtotals = Array(False, _False, False, False, False, False, False, False,

7、False, False, False, False)Range("A7").Select("數(shù)據(jù)透視表 1").PivotFields(" 名稱 1").Subtotals = Array(False, _False, False, False, False, False, False, False, False, False, False, False)("數(shù)據(jù)透視表 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("數(shù)據(jù)透視表 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").("數(shù)據(jù)透視表 1").PivotSelect"物料描述All”, xlLabelOnly, TrueColumns("F:F").SelectWith Selection.HorizontalAlignment = xlCenter.Vert