CODEWITHSUNDEEP
excelvbagraphcopy
MisterP
MisterP

Reputation: 1

Error copying waterfall charts with Excel macro

I have some simple VBA code that loops through a range of graphs in my Excel workbook and copies them to a PowerPoint presentation. It works great for all graphs except for waterfall graphics (Error 445: “Object doesn't support this option”). Since they're relatively new to Excel, I thought there might be an issue of compatibility and was wondering if there was a workaround.

Thanks !!

'Bar chart ==> works fine
ActiveWorkbook.Sheets(7).ChartObjects("Graphique 1").Copy
' Waterfall chart ==> Error 445
ActiveWorkbook.Sheets(8).ChartObjects("Graphique 1").Copy

Edit: When I try to record the procedure with Excel's macro recorder, it gives me the same lines of code, but when executing the code, I get the same error.

ActiveSheet.ChartObjects("Graphique 1").Activate
Selection.Copy

Upvotes: 0

Views: 1064

Answers (2)

Jon Peltier
Jon Peltier

Reputation: 6063

tldr; I stumbled on the following, which works for all charts, including waterfalls and other new Excel charts:

ActiveSheet.Shapes("Any Chart").Copy

Here is how to copy most Excel charts:

ActiveSheet.ChartObjects("My Chart").Copy

ActiveSheet.ChartObjects("My Chart").Chart.ChartArea.Copy

However, neither of the above work with a waterfall chart or other newer Excel chart.

If you record a macro while copying a waterfall chart, this is what you get:

Sub RecordedMacro1()
'
' Recorded while copying Waterfall chart
'
    ActiveSheet.ChartObjects("Waterfall Chart").Activate
    Selection.Copy
End Sub

This also also does not work with a waterfall chart.

There's plenty of other tried and true chart-related VBA that doesn't work with the newer Excel charts.

Upvotes: 0

Mr ML
Mr ML

Reputation: 428

I've tested with the waterfall chart and it does seem like VBA is not able to copy that type of chart.

Potential fixes: You're able to copy as a picture using .copyPicure or you can make a workaround by using:

YourchartObject.duplicate.select
selection.cut

and then pasting to the Powerpoint.

Upvotes: 1

Related Questions