VBA only running in debug mode

Question

What I'm trying to do is to copy a bunch of cells as a picture from one sheet and paste it in a chart object in another sheet. The following is the code used and it is running fine when used in debug mode, but I do no see the image being pasted in the chart when I run it normally.

Sub copy_paste_KDT()
'
' copy_paste_KDT Macro
'

'
    Worksheets("KDT").Range("J12:AB37").CopyPicture Appearance:=xlScreen, Format:=xlPicture

    Dim wb_path As String
    wb_path = Application.ThisWorkbook.Path

    'Dim objCht As ChartObject
    'Set objCht = ActiveSheet.ChartObjects("KDT Rectangle")

    'If Not objCht Is Nothing Then
    If ActiveSheet.ChartObjects.Count > 0 Then
        ActiveSheet.ChartObjects("KDT Rectangle").Delete
    End If

    With Worksheets("profile")

        'Creating the Chart
        .ChartObjects.Add(690, 125, 550, 245).Name = "KDT Rectangle"

    End With

    If Range("B11").Value = 0 Then
        With Worksheets("profile")

            Application.ScreenUpdating = True
            'Application.Wait (Now + TimeValue("00:00:01"))

            With .ChartObjects("KDT Rectangle")
                .Chart.Paste
            End With

        End With
    End If   
End Sub

I have also tried few thing like waiting for 1 to 10 seconds before the image is being pasted but of no use. Even tried putting a loop to count from 1 to a billion, no use again. Finally wanted to check if the image is getting pasted in a random cell of the sheet and that works, but not in the chart object.

I would appreciate if someone could help me figure out why the image is not getting paste.

TL,DR: Macro to copy paste a part of excel as a screenshot into a chart creates a chart successfully but not able to populate the image when run (F5), but works perfectly in debug mode (F8).

user_5309373 · Answer

Although I am using Excel 2010 and your code works fine in testing for me.

You can try putting a Select in before the .Chart.Paste this might help with pasting inside the chart. See code below, just added the line into your original code, so you were almost there.

Option Explicit

Sub copy_paste_KDT()
'
' copy_paste_KDT Macro
'

'
    Worksheets("KDT").Range("J12:AB37").CopyPicture Appearance:=xlScreen, Format:=xlPicture

    Dim wb_path As String
    wb_path = Application.ThisWorkbook.Path

    'Dim objCht As ChartObject
    'Set objCht = ActiveSheet.ChartObjects("KDT Rectangle")

    'If Not objCht Is Nothing Then
    If ActiveSheet.ChartObjects.Count > 0 Then
        ActiveSheet.ChartObjects("KDT Rectangle").Delete
    End If

    With Worksheets("profile")

        'Creating the Chart
        .ChartObjects.Add(690, 125, 550, 245).Name = "KDT Rectangle"

    End With

    If Range("B11").Value = 0 Then
        With Worksheets("profile")

            Application.ScreenUpdating = True
            'Application.Wait (Now + TimeValue("00:00:01"))

            With .ChartObjects("KDT Rectangle")
                .Select   'Just added this
                .Chart.Paste
            End With

        End With
    End If
End Sub

VBA only running in debug mode

1 Answers