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

2022-04-07 08:00:08 文档大全网 [ 字体：小中大 ] [ 阅读： ]

【#文档大全网# 导语】以下是®文档大全网的小编为您整理的《VBA实战——创建数据透视表》，欢迎阅读！
透视,实战,创建,数据,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

.