ActiveSheet.Pictures.Paste runtime error

[ms628255]

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

[Logit]

Please post a copy of your workbook (no confidential information).

[ms628255]

I´ve attached a very simple example with just two rows.

[Lylandra]

Hello,
ms628255 did you find a solution ? Or does anyone else have an idea ?

I have exactly the same issue : when I try to insert a picture or a graph, I get the Runtime Error 1004 "We couldn't paste this data because it took too long..."
But in Debug mode, it runs perfectly fine, it's instantaneous.

Please help !
Thanks,
Lyl

[Lylandra]

Update if someone else meets this problem: it seems to be due to some background process still running after working on other MS applications.
Anyway, after rebooting my computer, the issue disappeared.

[Logit]

I immediately receive an error "Invalid procedure call or argument." and the following lines are highlighted :


ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"ResUsage!R1C2:R1048576C2", Version:=6).CreatePivotTable TableDestination:= _
"days_count!R3C1", TableName:="PivotTable2", DefaultVersion:=6

[Paul_Hossler]

FWIW, cleanup up the macro to

a. make it easier for me to follow
b. avoid assuming which the active sheet
c. no selecting things

and didn't really have any problems with the logic





Option Explicit


Sub Macro2()
Dim pt As PivotTable

Application.ScreenUpdating = False

Worksheets.Add.Name = "days_count"

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:="ResUsage!R1C2:R1048576C2", Version:=6).CreatePivotTable _
TableDestination:="days_count!R3C1", TableName:="PivotTable2", DefaultVersion:=6


Set pt = Worksheets("days_count").PivotTables(1)


With pt
.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


With .PivotCache
.RefreshOnFileOpen = False
.MissingItemsLimit = xlMissingItemsDefault
End With

.RepeatAllLabels xlRepeatLabels

.AddDataField .PivotFields("LogDate"), "Count of LogDate", xlCount

With .PivotFields("LogDate")
.Orientation = xlRowField
.Position = 1
End With
End With


ActiveWorkbook.ShowPivotTableFieldList = True

With Worksheets("days_count")
.Shapes.AddChart2(227, xlLine).Select
ActiveChart.SetSourceData Source:=Range("days_count!$A$3:$B$12")
ActiveChart.Parent.Cut
End With

With Worksheets("ResUsage")
.Select
.Range("F1").Select ' <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
.Pictures.Paste
End With

Application.DisplayAlerts = False 'switching off the alert button
Worksheets("days_count").Delete
Application.DisplayAlerts = True

Application.ScreenUpdating = True


End Sub

[Logit]

Had to DIM Dim xlRepeatLabels
Still getting error message on :


ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:="ResUsage!R1C2:R1048576C2", Version:=6).CreatePivotTable _
TableDestination:="days_count!R3C1", TableName:="PivotTable2", DefaultVersion:=6

[Paul_Hossler]

I'm using Excel 365 and xlRepeatLabels is a built in constant


31087