codelearner
New Member
- Joined
- Jul 14, 2020
- Messages
- 1
- Office Version
- 365
- 2016
- Platform
- Windows
- Mobile
- Web
VBA Code:
Sub Run()
'
' Run Macro
' Run
'
' Keyboard Shortcut: Ctrl+Shift+Q
'
Columns("A:O").Select
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"'OPS_Defect Report'!R1C1:R1048576C15", Version:=6).CreatePivotTable _
TableDestination:="'OPS_Defect Report'!R2C17", TableName:="PivotTable7", _
DefaultVersion:=6
Sheets("OPS_Defect Report").Select
Cells(2, 17).Select
ActiveWindow.SmallScroll ToRight:=11
With ActiveSheet.PivotTables("PivotTable7").PivotFields("Station")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable7").PivotFields("Defect")
.Orientation = xlColumnField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable7").AddDataField ActiveSheet.PivotTables( _
"PivotTable7").PivotFields("Defect Qty"), "Count of Defect Qty", xlCount
With ActiveSheet.PivotTables("PivotTable7").PivotFields("Count of Defect Qty")
.Caption = "Sum of Defect Qty"
.Function = xlSum
End With
With ActiveSheet.PivotTables("PivotTable7").PivotFields("Station")
.PivotItems("(blank)").Visible = False
End With
ActiveSheet.PivotTables("PivotTable7").PivotFields("Station").AutoSort _
xlDescending, "Sum of Defect Qty"
With ActiveSheet.PivotTables("PivotTable7").PivotFields("Defect")
.PivotItems("(blank)").Visible = False
End With
ActiveWindow.SmallScroll ToRight:=5
Range("R3:AD3").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 90
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("R:AD").Select
Columns("R:AD").EntireColumn.AutoFit
ActiveWindow.LargeScroll ToRight:=-1
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
Selection.ColumnWidth = 8.27
Range("S5").Select
ActiveSheet.PivotTables("PivotTable7").PivotSelect "Station[All]", xlLabelOnly _
+ xlFirstRow, True
ActiveChart.ClearToMatchStyle
ActiveSheet.Shapes.AddChart2(297, xlColumnStacked).Select
ActiveChart.SetSourceData Source:=Range("'OPS_Defect Report'!$Q$2:$AD$13")
ActiveWindow.SmallScroll ToRight:=4
ActiveSheet.Shapes("Chart 6").IncrementLeft 270
ActiveSheet.Shapes("Chart 6").IncrementTop 180.5882677165
Range("AA18").Select
End Sub
Attachments
Last edited by a moderator: