AW: VBA - Pivot Tab. - Laufzeitfehler 1004
13.04.2020 12:07:57
Peter
Hallo Regina,
besten Dank für deine schnelle Unterstützung. Ich habe vor immer andere, leicht unterschiedliche Auswertungen zu erzeugen und daher erstelle ich die Pivot Tabelle nach mehreren Abfragen immer wieder neu.
Ich habe deinen Rat befolgt und habe nun folgendes geändert...
Columns("A:U").Select
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"import!R1C1:R1048576C21", Version:=6).CreatePivotTable TableDestination:= _
"Tabelle2!R3C1", TableName:="PivotTable1", DefaultVersion:=6
Worksheets(1).Select
Cells(3, 1).Select
"Sheets.Add" wurde entfernt und mit "Worksheets(1).Select" spreche ich immer den ersten Tab an. Nun knallt es aber etwas tiefer im Code :(
Vielleicht kannst Du mir auch da einen Tipp geben?
Vielen lieben Dank.
Sub Makro1()
' Makro1 Makro
Columns("A:U").Select
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"import!R1C1:R1048576C21", Version:=6).CreatePivotTable TableDestination:= _
"Tabelle2!R3C1", TableName:="PivotTable1", DefaultVersion:=6
Worksheets(1).Select
With ActiveSheet.PivotTables("PivotTable1")
.ColumnGrand = True
.HasAutoFormat = True
.DisplayErrorString = False
.DisplayNullString = True
.EnableDrilldown = True
.ErrorString = ""
.MergeLabels = False
.NullString = ""
.PageFieldOrder = 2
.PageFieldWrapCount = 0
.PreserveFormatting = True
.RowGrand = True
.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
End With
With ActiveSheet.PivotTables("PivotTable1").PivotCache
.RefreshOnFileOpen = False
.MissingItemsLimit = xlMissingItemsDefault
End With
ActiveSheet.PivotTables("PivotTable1").RepeatAllLabels xlRepeatLabels
With ActiveSheet.PivotTables("PivotTable1").PivotFields("PRIORITY")
.Orientation = xlPageField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("INCIDENT_NUMBER"), "Anzahl von INCIDENT_NUMBER", _
xlCount
With ActiveSheet.PivotTables("PivotTable1").PivotFields("SLT_STATUS")
.Orientation = xlColumnField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("SLT_STATUS"), "Anzahl von SLT_STATUS", xlCount
With ActiveSheet.PivotTables("PivotTable1").PivotFields("CLOSED_DATE")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").PivotFields("CLOSED_DATE").AutoGroup
ActiveSheet.PivotTables("PivotTable1").PivotFields("Quartale").Orientation = _
xlHidden
Range("C6").Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Anzahl von SLT_STATUS" _
)
.Calculation = xlPercentOfRow
.NumberFormat = "0,00%"
End With
ActiveSheet.PivotTables("PivotTable1").PivotFields("PRIORITY").CurrentPage = _
"(All)"
With ActiveSheet.PivotTables("PivotTable1").PivotFields("PRIORITY")
.PivotItems("(blank)").Visible = False
End With
ActiveSheet.PivotTables("PivotTable1").PivotFields("PRIORITY"). _
EnableMultiplePageItems = True
ActiveSheet.Shapes.AddChart2(297, xlColumnStacked).Select
ActiveChart.SetSourceData Source:=Range("Tabelle2!$A$3:$G$9")
ActiveSheet.Shapes("Diagramm 1").ScaleWidth 2.0114584427, msoFalse, _
msoScaleFromTopLeft
ActiveSheet.Shapes("Diagramm 1").ScaleHeight 1.9843751823, msoFalse, _
msoScaleFromTopLeft
ActiveChart.FullSeriesCollection(4).Select
Selection.Format.Fill.Visible = msoFalse
ActiveChart.FullSeriesCollection(4).ApplyDataLabels
ActiveChart.FullSeriesCollection(3).Select
ActiveChart.FullSeriesCollection(3).ApplyDataLabels
ActiveChart.FullSeriesCollection(1).Select
ActiveChart.FullSeriesCollection(1).ApplyDataLabels
ActiveChart.ChartArea.Select
ActiveSheet.Shapes("Diagramm 1").IncrementLeft -411.75
ActiveSheet.Shapes("Diagramm 1").IncrementTop 16.5
ActiveWorkbook.SlicerCaches.Add2(ActiveSheet.PivotTables("PivotTable1"), _
"CLOSED_DATE", , xlTimeline).Slicers.Add ActiveSheet, , "CLOSED_DATE", _
"CLOSED_DATE", 300.75, 470.25, 262.5, 108
ActiveSheet.Shapes.Range(Array("CLOSED_DATE")).Select
ActiveSheet.Shapes("CLOSED_DATE").IncrementLeft 271.5
ActiveSheet.Shapes("CLOSED_DATE").IncrementTop -105
End Sub
15. Zeile von unten -> ActiveChart.FullSeriesCollection(4).ApplyDataLabels
Da kommt nun der gleiche Fehler.
Weißt Du auch hier einen Rat?
Danke sehr.
Gruß
Peter