AW: Pivottabelle / Diagramm per VBA
23.10.2020 15:33:09
Matthias
btw der Code
Set wksData1 = ActiveWorkbook.Worksheets(1)
With wksData1
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
.Range(.Cells(1, 1), .Cells(lzv2, 6)), Version:=6).CreatePivotTable TableDestination:= _
_
_
.Range("M1"), TableName:="PivotTable1", DefaultVersion:=6
'Pivot-Tabellenbericht der variablen zuordnen
Set pvTab1 = .PivotTables(1)
With pvTab1
'Grundeinstellungen des Pivot-tabellenberichts
.HasAutoFormat = True
.DisplayErrorString = False
.DisplayNullString = True
.EnableDrilldown = True
.ErrorString = ""
.MergeLabels = False
.NullString = ""
.PageFieldOrder = 2
.PageFieldWrapCount = 0
.PreserveFormatting = True
.ColumnGrand = False
.RowGrand = False
.SaveData = True
.PrintTitles = False
.RepeatItemsOnEachPrintedPage = True
.TotalsAnnotation = False
.CompactRowIndent = 1
.InGridDropZones = False
.DisplayFieldCaptions = True
.DisplayMemberPropertyTooltips = False
.DisplayContextTooltips = True
.ShowDrillIndicators = True
.PrintDrillIndicators = False
.AllowMultipleFilters = False
.SortUsingCustomLists = True
.FieldListSortAscending = False
.ShowValuesRow = False
.CalculatedMembersInFilters = False
.RowAxisLayout xlCompactRow
With .PivotCache
.RefreshOnFileOpen = False
.MissingItemsLimit = xlMissingItemsNone
End With
.RepeatAllLabels xlRepeatLabels
'Zeilenfeld anlegen
With .PivotFields("Pos. auf Tour")
.Orientation = xlRowField
.Position = 1
End With
'Spaltenfeld
With .PivotFields("Datei")
.Orientation = xlColumnField
.Position = 1
End With
'Datenfeld mit Funktion Mittelwert anlegen
.AddDataField .PivotFields("Weg pro Position"), "Mittelwert von Weg pro Position", _
xlAverage
'zahlenformat des Datenfelds festlegen
With .PivotFields("Mittelwert von Weg pro Position")
.NumberFormat = "0.0"
End With
End With
'Diagramm im Tabellenblatt anlegen
.Shapes.AddChart2 201, xlColumnClustered
Set objChartObj1 = .ChartObjects(1)
End With
Set objChart1 = objChartObj1.Chart
objChart1.SetSourceData Source:=pvTab1.DataBodyRange 'Range("Ebene1!$H$1:$I$5")
' Charts(objChart1).HasLegend = True '''''''''''''''''''''''''''''''''''''''''''''''''
With objChartObj1
.Top = wksData1.Cells(6, 8).Top
.Left = wksData1.Cells(6, 8).Left
.Width = 600
.Height = 400
End With
Habe das wie hier --> Charts(objChart1).HasLegend = True
Die Fehlermeldung sagt aus, dass die Typen unverträglich sind.
Ohne die Zeile funktioniert das makro