1、Sub 创建数据透视表() ActiveWorkbook.Names.Add Name:=database1, RefersToR1C1:= _ =OFFSET(R4C3,COUNTA(C3),COUNTA(R4) ActiveWorkbook.Names(database1).Comment = Rows(5:5).Select Selection.Delete Shift:=xlUp Range(C4).Select Sheets.Add ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _ dat
2、abase1, Version:=xlPivotTableVersion14).CreatePivotTable _ TableDestination:=Sheet1!R3C1, TableName:=数据透视表1, DefaultVersion:= _ xlPivotTableVersion14 Sheets(Sheet1).Select Cells(3, 1).Select With ActiveSheet.PivotTables(数据透视表1).PivotFields(指令单单号) .Orientation = xlPageField .Position = 1 End With Wit
3、h ActiveSheet.PivotTables(数据透视表1).PivotFields(名称 1) .Orientation = xlRowField .Position = 1 End With With ActiveSheet.PivotTables(数据透视表1).PivotFields(作业) .Orientation = xlRowField .Position = 2 End With With ActiveSheet.PivotTables(数据透视表1).PivotFields(物料) .Orientation = xlRowField .Position = 3 End
4、With With ActiveSheet.PivotTables(数据透视表1).PivotFields(物料描述) .Orientation = xlRowField .Position = 4 End With With ActiveSheet.PivotTables(数据透视表1).PivotFields(供应商) .Orientation = xlRowField .Position = 5 End With ActiveSheet.PivotTables(数据透视表1).AddDataField ActiveSheet.PivotTables(数据透视表1 _ ).PivotFie
5、lds(需求量), 求和项:需求量, xlSum Range(B21).Select With ActiveSheet.PivotTables(数据透视表1) .InGridDropZones = True .RowAxisLayout xlTabularRow End With Range(D6).Select ActiveSheet.PivotTables(数据透视表1).PivotFields(物料描述).Subtotals = Array(False, _ False, False, False, False, False, False, False, False, False, Fa
6、lse, False) Range(C6).Select ActiveSheet.PivotTables(数据透视表1).PivotFields(物料).Subtotals = Array(False, _ False, False, False, False, False, False, False, False, False, False, False) Range(B6).Select ActiveSheet.PivotTables(数据透视表1).PivotFields(作业).Subtotals = Array(False, _ False, False, False, False,
7、 False, False, False, False, False, False, False) Range(A7).Select ActiveSheet.PivotTables(数据透视表1).PivotFields(名称 1).Subtotals = Array(False, _ False, False, False, False, False, False, False, False, False, False, False) ActiveSheet.PivotTables(数据透视表1).PivotSelect , xlDataAndLabel, True Selection.Bo
8、rders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndSha
9、de = 0 .Weight = xlThin End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders
10、(xlInsideVertical) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlInsideHorizontal) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With Range(A13).Select ActiveSheet.PivotTables(数据透视表1).ShowDrillIndicat
11、ors = False Columns(A:A).ColumnWidth = 7.88 Columns(B:F).Select With Selection .HorizontalAlignment = xlLeft .VerticalAlignment = xlCenter .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Columns(B:F).E
12、ntireColumn.AutoFit ActiveSheet.PivotTables(数据透视表1).PivotSelect 物料描述All, xlLabelOnly, True Columns(F:F).Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End WithEnd Sub
