CODEWITHSUNDEEP
excelvbaexcel-charts
Abel Jacob
Abel Jacob

Reputation: 1

Copying Chart Objects failed - VBA glitch?

I run a program that executes a series of macros to manipulate raw data into various charts and graphs. The master macro runs about 15 macros in order. Everything works about 50% of the time. The code to copy all my charts and put them into one sheet is the bottleneck. It works half the time and the other half I get the following error:

"Method of Copy Object Chart Object failed."

See code below. Open to any and all suggestions, I am stumped!

Sub CopyCharts()

     Dim Sheet_Count As Integer
     Dim Target_Sheet As Worksheet
     Dim i As Integer
     Dim Cht As ChartObject
     
     Sheet_Count = ActiveWorkbook.Sheets.Count
     Set Target_Sheet = ActiveWorkbook.Sheets(4)
     
     For i = 5 To 16
        For Each Cht In ActiveWorkbook.Sheets(i).ChartObjects
            Cht.Copy
            Target_Sheet.Paste Target_Sheet.Range("D4")
        Next Cht
     Next i

End Sub

Upvotes: 0

Views: 1341

Answers (1)

CoderGuy
CoderGuy

Reputation: 31

I had this same issue just today and, after reading your question, remembered I've had it before. I remembered the trick is to clear the clipboard before each copy operation. To do this, use the line of code:

Application.CutCopyMode = False

Put it right before your copy like this:

 Sub CopyCharts()

 Dim Sheet_Count As Integer
 Dim Target_Sheet As Worksheet
 Dim i As Integer
 Dim Cht As ChartObject
 
 Sheet_Count = ActiveWorkbook.Sheets.Count
 Set Target_Sheet = ActiveWorkbook.Sheets(4)
 
 For i = 5 To 16
    For Each Cht In ActiveWorkbook.Sheets(i).ChartObjects
        Application.CutCopyMode = False 'Clear clipboard before copy operation.
        Cht.Copy
        Target_Sheet.Paste Target_Sheet.Range("D4")
    Next Cht
 Next i

End Sub

Upvotes: 3

Related Questions