Replace all charts with pictures on a worksheet?

daviddiebel
daviddiebel used Ask the Experts™
on
I'm trying to copy all charts on a worksheet as pictures, and then delete all of the original charts.  What I have for code right now is:

Sub ConvertCharttoPicture()

Dim i As Integer
Dim intX As Integer

For i = 1 To ActiveSheet.ChartObjects.Count
        ActiveSheet.ChartObjects(i).Activate
        ActiveSheet.ChartObjects(i).Select
        ActiveChart.ChartArea.Select
        ActiveChart.CopyPicture _
        Appearance:=xlPrinter, Size:=xlScreen, Format:= _
        xlPicture
        ActiveChart.Paste

Next

End Sub

One problem is that I cannot figure out how to delete the original chart.  The second problem is that this loops indefinitely, going over the same chart, again and again.  Any assistance at making this work would be great!  I intend to use this on many, many worksheets as part of another macro.

Thank you!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
To delete the chart try this:

ActiveSheet.ChartObjects(i).Delete
Mechanical Engineer
Most Valuable Expert 2013
Top Expert 2013
Commented:
If you want to delete the charts, then go backwards through the collection:

Sub ConvertCharttoPicture()
Dim i As Integer
Application.ScreenUpdating = False
With ActiveSheet
    For i = .ChartObjects.Count To 1 Step -1
        .ChartObjects(i).Chart.CopyPicture Appearance:=xlPrinter, Size:=xlScreen, Format:=xlPicture
        .ChartObjects(i).TopLeftCell.PasteSpecial
        .ChartObjects(i).Delete
    Next
End With
End Sub

Open in new window

Author

Commented:
Thank you - perfect!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial