Consulting

Results 1 to 9 of 9

Thread: ActiveSheet.Pictures.Paste runtime error

Threaded View

Previous Post Previous Post   Next Post Next Post
  1. #1
    VBAX Newbie
    Joined
    Feb 2022
    Posts
    2
    Location

    ActiveSheet.Pictures.Paste runtime error

    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
    Last edited by Aussiebear; 02-07-2022 at 09:31 AM. Reason: Added code tags to supplied code

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •