VBA Copying chartareas

[Johnny C]

Greetings.

The following code isn't working.

All the literature says activate the sheet and select the chart area and copy the selection, and that works. However I don't want to select if I can avoid it. I don't see why this won't work but it doesn't.

For Each sht In ActiveWorkbook.Sheets
    For Each cht In sht.ChartObjects
           cht.ChartArea.Copy

the error is on the copy line : Error 438, Object doesn't support this property or method

TIA

 

[tlowry]

Here is a bit of code that might help.

When the code gets to the cht.type (MsgBox), the chart is in/on the Clip-Board (Paste it)

Please post results/questions

Sub DisplayChartNames()
    Dim cht
        For Each cht In Sheets("Demo").Shapes()
            Select Case cht.Type
                Case msoChart
                    MsgBox cht.Name
                    cht.Copy
                    
                    '=========================================
                    ' do what you want here to put the chart
                    'in it's place (Paste)
                    '=========================================
                    
                Case Else

            End Select
        Next
End Sub

 

[John_w]

I think the error is because ChartArea isn't a property of the ChartObject object (cht). Try instead:

        cht.Chart.ChartArea.Copy

 

[Johnny C]

Hi, I've tried that and it didn't work.

I've hunted around a far bit over this, and I've not found anywhere that doesn't use activechart.chartarea.copy so I'm wondering if it's a limitation that the chart has to be activated

I could select each sheet and turn screen updating but I'd like to solve it as its caused me problems in the past