Verbesserungspotential
28.01.2015 15:57:25
Emanuel
ich habe einen Code geschrieben, der es mir erlaubt aus einem Datenreporting (mit einigen ausgeblendeten unnötigen Zellen) die Daten, die ich benötige auszusortieren und in eine Pivot zu setzen.
Folgenden Code habe ich dafür erstellt:
Sub Vis()
Application.ScreenUpdating = False
Cells.EntireColumn.Hidden = False
If ActiveSheet.AutoFilterMode Then
ActiveSheet.Rows("1:1").AutoFilter
End If
ActiveSheet.Columns("A:L").Delete
Range("A15").Value = "Advert ID"
ActiveSheet.Columns("B").Delete
Range("B15").Value = "Site ID"
ActiveSheet.Columns("C").Delete
Range("C15").Value = "Slot ID"
ActiveSheet.Columns("D").Delete
Range("D15").Value = "Format"
ActiveSheet.Columns("E:R").Delete
ActiveSheet.Columns("H:L").Delete
ActiveSheet.Columns("J:N").Delete
ActiveSheet.Columns("L:P").Delete
ActiveSheet.Columns("N:BM").Delete
Range("E15").Value = "AI served with Vis Pixel"
Range("F15").Value = "Measured AI 50% / 1sec"
Range("G15").Value = "Visible AI 50% / 1sec"
Range("H15").Value = "Measured AI 60% / 1sec"
Range("I15").Value = "Visible AI 60% / 1sec"
Range("J15").Value = "Measured AI 70% / 2sec"
Range("K15").Value = "Visible AI 70% / 2sec"
Range("L15").Value = "Measured AI 75% / 1sec"
Range("M15").Value = "Visible AI 75% / 1sec"
Application.DisplayAlerts = False
Sheets("View Time Classes").Delete
Sheets("Devices").Delete
Sheets("Glossary").Delete
Application.DisplayAlerts = True
Range("M15").Select
Range(Selection, Selection.End(xlToLeft)).Select
Range(Selection, Selection.End(xlDown)).Select
lastRow = Cells(Rows.Count, 13).End(xlUp).Row
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Report!R15C1:R" & lastRow & "C13", Version:=xlPivotTableVersion15).CreatePivotTable _
TableDestination:="Tabelle1!R3C1", TableName:="PivotTable1", _
DefaultVersion:=xlPivotTableVersion15
Sheets("Tabelle1").Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Slot ID")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").CalculatedFields.Add "Vis 50/1", _
"='Visible AI 50% / 1sec' /'Measured AI 50% / 1sec'", True
ActiveSheet.PivotTables("PivotTable1").PivotFields("Vis 50/1").Orientation = _
xlDataField
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Summe von Vis 50/1")
.NumberFormat = "0.00%"
End With
ActiveSheet.PivotTables("PivotTable1").CalculatedFields.Add "Vis 60/1", _
"='Visible AI 60% / 1sec' /'Measured AI 60% / 1sec'", True
ActiveSheet.PivotTables("PivotTable1").PivotFields("Vis 60/1").Orientation = _
xlDataField
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Summe von Vis 60/1")
.NumberFormat = "0.00%"
End With
ActiveSheet.PivotTables("PivotTable1").CalculatedFields.Add "Vis 75/1", _
"='Visible AI 75% / 1sec'/'Measured AI 75% / 1sec'", True
ActiveSheet.PivotTables("PivotTable1").PivotFields("Vis 75/1").Orientation = _
xlDataField
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Summe von Vis 75/1")
.NumberFormat = "0.00%"
End With
ActiveSheet.PivotTables("PivotTable1").CalculatedFields.Add "Vis 70/2", _
"='Visible AI 70% / 2sec' /'Measured AI 70% / 2sec'", True
ActiveSheet.PivotTables("PivotTable1").PivotFields("Vis 70/2").Orientation = _
xlDataField
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Summe von Vis 70/2")
.NumberFormat = "0.00%"
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Measured AI 50% / 1sec"), _
"Summe von Measured AI 50% / 1sec", xlSum
With ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"Summe von Measured AI 50% / 1sec")
.NumberFormat = "#,##0"
End With
Range("B4").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields("Slot ID").AutoSort _
xlAscending, "Summe von Vis 50/1", ActiveSheet.PivotTables("PivotTable1"). _
PivotColumnAxis.PivotLines(1), 1
Range("A3").Select
ActiveSheet.PivotTables("PivotTable1").CompactLayoutRowHeader = "Slot ID"
Range("B3").Select
ActiveSheet.PivotTables("PivotTable1").DataPivotField.PivotItems( _
"Summe von Vis 50/1").Caption = "Visibility 50% / 1sec"
Range("C3").Select
ActiveSheet.PivotTables("PivotTable1").DataPivotField.PivotItems( _
"Summe von Vis 60/1").Caption = "Visibility 60% / 1sec"
Range("D3").Select
ActiveSheet.PivotTables("PivotTable1").DataPivotField.PivotItems( _
"Summe von Vis 75/1").Caption = "Visibility 75% / 2sec"
Range("E3").Select
ActiveSheet.PivotTables("PivotTable1").DataPivotField.PivotItems( _
"Summe von Vis 70/2").Caption = "Visibility 70% / 2sec"
Range("F3").Select
ActiveSheet.PivotTables("PivotTable1").DataPivotField.PivotItems( _
"Summe von Measured AI 50% / 1sec").Caption = "Anzahl Measured AI 50% / 1sec"
ActiveSheet.PivotTables("PivotTable1").RowAxisLayout xlTabularRow
Columns("A:A").Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("B:B").Select
Selection.ColumnWidth = 23
Columns("C:C").Select
Selection.ColumnWidth = 23
Columns("D:D").Select
Selection.ColumnWidth = 23
Columns("E:E").Select
Selection.ColumnWidth = 23
Columns("F:F").Select
Selection.ColumnWidth = 35
Range("B3:F3").Select
With Selection
.HorizontalAlignment = xlRight
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("A1").Select
Sheets("Tabelle1").Select
Sheets("Tabelle1").Name = "Slot Visibility"
End Sub
Falls das zu viel verlangt ist, sagt es mir einfach. Ich verstehe, wenn sich jemand nicht die Mühe machen will. Ich erhoffe mir daraus, schlechte Codierung in Zukunft zu vermeiden.
Vielen Dank im Voraus und liebe Grüße,
Emanuel