ms628255
02-07-2022, 08:09 AM
Hello,
I am going crazy! Here is my problem: I want to insert the picture of a chart in the current sheet where all the days are listed with the frequency. For this I wrote the following code:
Sub Macro2()
' Macro2 Macro
Application.DisplayAlerts = False 'switching off the alert button
Sheets("ResUsage").Select
Columns("B:B").Select
Sheets.Add.Name = "days_count"
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"ResUsage!R1C2:R1048576C2", Version:=6).CreatePivotTable TableDestination:= _
"days_count!R3C1", TableName:="PivotTable2", DefaultVersion:=6
Sheets("days_count").Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable2")
.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("PivotTable2").PivotCache
.RefreshOnFileOpen = False
.MissingItemsLimit = xlMissingItemsDefault
End With
ActiveSheet.PivotTables("PivotTable2").RepeatAllLabels xlRepeatLabels
ActiveWorkbook.ShowPivotTableFieldList = True
ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
"PivotTable2").PivotFields("LogDate"), "Count of LogDate", xlCount
With ActiveSheet.PivotTables("PivotTable2").PivotFields("LogDate")
.Orientation = xlRowField
.Position = 1
End With
Columns("A:B").Select
ActiveSheet.Shapes.AddChart2(227, xlLine).Select
ActiveChart.SetSourceData Source:=Range("days_count!$A$3:$B$12")
ActiveChart.Parent.Cut
Sheets("ResUsage").Select
Range("CJ65").Select
ActiveSheet.Pictures.Paste
Sheets("days_count").Select
ActiveWindow.SelectedSheets.Delete
End Sub
When I run the macro step by step, I get no errors and it runs very quickly.
However, when I run the macro completely (with F5 or also Run Macro), I´m getting a Runtime Error 1004 We couldn't paste this data because it took too long ....
If I then go to Debug and press F8 or also F5, it continues without problems.
What do I have to do?
Any help is appreciated.
Regards
Michael
I am going crazy! Here is my problem: I want to insert the picture of a chart in the current sheet where all the days are listed with the frequency. For this I wrote the following code:
Sub Macro2()
' Macro2 Macro
Application.DisplayAlerts = False 'switching off the alert button
Sheets("ResUsage").Select
Columns("B:B").Select
Sheets.Add.Name = "days_count"
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"ResUsage!R1C2:R1048576C2", Version:=6).CreatePivotTable TableDestination:= _
"days_count!R3C1", TableName:="PivotTable2", DefaultVersion:=6
Sheets("days_count").Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable2")
.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("PivotTable2").PivotCache
.RefreshOnFileOpen = False
.MissingItemsLimit = xlMissingItemsDefault
End With
ActiveSheet.PivotTables("PivotTable2").RepeatAllLabels xlRepeatLabels
ActiveWorkbook.ShowPivotTableFieldList = True
ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
"PivotTable2").PivotFields("LogDate"), "Count of LogDate", xlCount
With ActiveSheet.PivotTables("PivotTable2").PivotFields("LogDate")
.Orientation = xlRowField
.Position = 1
End With
Columns("A:B").Select
ActiveSheet.Shapes.AddChart2(227, xlLine).Select
ActiveChart.SetSourceData Source:=Range("days_count!$A$3:$B$12")
ActiveChart.Parent.Cut
Sheets("ResUsage").Select
Range("CJ65").Select
ActiveSheet.Pictures.Paste
Sheets("days_count").Select
ActiveWindow.SelectedSheets.Delete
End Sub
When I run the macro step by step, I get no errors and it runs very quickly.
However, when I run the macro completely (with F5 or also Run Macro), I´m getting a Runtime Error 1004 We couldn't paste this data because it took too long ....
If I then go to Debug and press F8 or also F5, it continues without problems.
What do I have to do?
Any help is appreciated.
Regards
Michael