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).

Are you sure that you don't have an issue with activesheet and activeworkbook? — FunThomas
– FunThomas, Commented Sep 21, 2017 at 7:22
@FunThomas the code is what I copy pasted as is and is working fine when running in debug mode (F8) — StrikeR
– StrikeR, Commented Sep 21, 2017 at 7:29
I am using Excel 2010 and it work fine as well. When you use Range("B11").Value = 0 it will take the value from the active page, rather add the specific Sheet you want to look at like Worksheets("profile").Range("B11").Value = 0 . Also just try putting .Select before the .Chart.Paste just for testing to see if your chart is actually accessible. — Jean-Pierre Oosthuizen
– Jean-Pierre Oosthuizen, Commented Sep 21, 2017 at 8:16
@Jean-PierreOosthuizen you are a genius. .Select did it for me. Please add it as an answer, will accept it. Also just an add on question but not a necessary one, is there a way of deselecting the chart object without selecting any cell on the sheet? — StrikeR
– StrikeR, Commented Sep 21, 2017 at 8:20

Jean-Pierre Oosthuizen · Accepted Answer · 2017-09-21 08:53:52Z

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

Collectives™ on Stack Overflow

VBA only running in debug mode

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related