【#第一文档网# 导语】以下是®第一文档网的小编为您整理的《VBA实战——创建数据透视表》,欢迎阅读!
. 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:= _ "database1", 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 With 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 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" _ ).PivotFields("需求量"), "求和项:需求量", 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, False, 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, 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.Borders(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 .TintAndShade = 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(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").ShowDrillIndicators = 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").EntireColumn.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 With End Sub . 本文来源:https://www.dywdw.cn/f1bc1765306c1eb91a37f111f18583d049640fa6.html