|
Hallo zusammen,
ich möchte über eine Schaltfläche eine Pivot-Tabelle erstellen lassen, dies ist auch kein Problem.
Nur die Programmierung im VBA läuft nicht, Laufzeitfehler.
Hier der Code, der Fehler liegt im Fett markierten Bereich.
Vielleicht hat jemand von euch ne Idee.
Danke schonmal im Voraus.
Sub CreatePivot()
' Creates a PivotTable report from the table on Projektaufwand
' by using the PivotTableWizard method with the PivotFields
' method to specify the fields in the PivotTable.
Dim objTable As PivotTable, objField As PivotField
ActiveWorkbook.Sheets("Tabelle1").Select
Range("A1").Select
Set objTable = Sheet1.PivotTableWizard
' Specify row fields.
Set objField = objTable.PivotFields("Spalte1")
objField.Orientation = xlRowField
objField.Position = 1
Set objField = objTable.PivotFields("Spalte2")
objField.Orientation = xlRowField
objField.Position = 2
Set objField = objTable.PivotFields("Spalte3")
objField.Orientation = xlRowField
objField.Position = 3
Set objField = objTable.PivotFields("Spalte4")
objField.Orientation = xlRowField
objField.Position = 4
' Specify a data field with its summary
' function and format.
Set objField = objTable.PivotFields("Spalte5")
objField.Orientation = xlDataField
objField.Function = xlSum
objField.NumberFormat = "#,##"
objField.Position = 1
Set objField = objTable.PivotFields("Spalte6")
objField.Orientation = xlDataField
objField.Function = xlSum
objField.NumberFormat = "#,##"
objField.Position = 2
Set objField = objTable.PivotFields("Spalte7")
objField.Orientation = xlDataField
objField.Function = xlSum
objField.NumberFormat = "#,##"
objField.Position = 3
Set objField = objTable.PivotFields("Anzahl geplanter Projekttage")
objField.Orientation = xlDataField
objField.Function = xlMax
objField.NumberFormat = "#,##"
objField.Position = 4
Set objField = objTable.PivotFields("Spalte8")
objField.Orientation = xlDataField
objField.Function = xlSum
objField.NumberFormat = "#,##"
objField.Position = 5
Set objField = objTable.PivotFields("Spalte9")
objField.Orientation = xlDataField
objField.Function = xlMin
objField.NumberFormat = "#,##"
objField.Position = 6
Set objField = objTable.PivotFields("Spalte10")
objField.Orientation = xlDataField
objField.Function = xlSum
objField.NumberFormat = "#,##"
objField.Position = 7
Set objField = objTable.PivotFields("Spalte11")
objField.Orientation = xlDataField
objField.Function = xlSum
objField.NumberFormat = "#,##"
objField.Position = 8
' Specify a page field.
Set objField = objTable.PivotFields("Spalte12")
objField.Orientation = xlPageField
objField.Position = 1
Set objField = objTable.PivotFields("Spalte13")
objField.Orientation = xlPageField
objField.Position = 2
Set objField = objTable.PivotFields("Spalte14")
objField.Orientation = xlPageField
objField.Position = 3
Set objField = objTable.PivotFields("Spalte15")
objField.Orientation = xlPageField
objField.Position = 4
' Preview the new PivotTable report.
ActiveSheet.PrintPreview
' Prompt the user whether to delete the PivotTable.
Application.DisplayAlerts = False
If MsgBox("Delete the PivotTable?", vbYesNo) = vbYes Then
ActiveSheet.Delete
End If
Application.DisplayAlerts = True
End Sub
|