VBA to copy/paste a chart

[andygill]

Hello

Could you help me.

I have some vba code that copies a chart as a picture to a sheet called destination.

The code runs in a loop updating the chart and coying to destination for each line of data (30 lines).

Everytime I run the code at random points in the process (that is not the same graph each time).

I get either runtime error Method copy of object 'ChartArea' failed at the line ActiveChart.ChartArea.Copy or

or runtime error '1004' Unable to get the pastespecial property of the worksheet class at the line ws = ActiveSheet.PasteSpecial(Format:="Picture (PNG)", Link:=False, DisplayAsIcon:=False).

If I click on the debug and then run sub button I carries on with the process of copying the graphs.

I've attached my code.

Thanks for your help.

 

[Domenic]

First, to copy the chart, you can use the CopyPicture method of the ChartObject object...

Worksheets("Front Sheet").ChartObjects("Chart 2").CopyPicture xlScreen, xlPicture

Then, you can simply select the destination worksheet and range, and then paste...

Worksheets("Destination").Select
Cells(pasterow, 1).Select
ActiveSheet.Paste

Then, if needed, you can refer to the pasted chart as follows...

With ActiveSheet
    With .Shapes(.Shapes.Count)
        'set properties here
        '
        '
    End With
End With

Hope this helps!

 

[andygill]

Thank you Domenic.

I still get the same error at either the copy or oaaste. Selecting Debug and then pressing the Run button the procedure continues and copies all the relevant graphs.

 

[Domenic]

Can you post the exact code that you're using? However, please don't use images to display your code. Instead, simply copy and paste your code within code tags, which you can find on the formatting bar above the message box.

Also, please confirm which line or lines cause an error, and the exact error messages you receive.

 

[THEKINGSLG]

Hey.

I have a problem with copying and pasting a chart. The error says "error of compilation. Not defined Sub or Function" and mark the word workbooks in blue.

Sub Funcion_2()

' 7. Select chart of new file
Woorkbooks ("X.xlsx")
.Worksheets("multiTimeline").ChartObjects("Chart_1").CopyPicture xlScreen, xlPicture

Worksheets("Sheet1").Select
Cells(C9, 1).Select
ActiveSheet.Paste

End Sub

Help please

 

[Domenic]

First of all, it should be Workbooks, not Woorbooks. Secondly, you have an illegal line break. So it should be . . .

Workbooks("X.xlsx").Worksheets("multiTimeline").ChartObjects("Chart_1").CopyPicture xlScreen, xlPicture

By the way, the last part of your code can be re-written more efficiently. So your code can be re-written as follows . . .

Sub Funcion_2()

    ' 7. Select chart of new file
    Workbooks("X.xlsx").Worksheets("multiTimeline").ChartObjects("Chart_1").CopyPicture xlScreen, xlPicture
  
    With Worksheets("Sheet1")
        .Paste .Range("C9")
    End With

End Sub

Hope this helps!

 

[THEKINGSLG]

First of all, it should be Workbooks, not Woorbooks. Secondly, you have an illegal line break. So it should be . . .

Workbooks("X.xlsx").Worksheets("multiTimeline").ChartObjects("Chart_1").CopyPicture xlScreen, xlPicture

By the way, the last part of your code can be re-written more efficiently. So your code can be re-written as follows . . .

Sub Funcion_2()

    ' 7. Select chart of new file
    Workbooks("X.xlsx").Worksheets("multiTimeline").ChartObjects("Chart_1").CopyPicture xlScreen, xlPicture
 
    With Worksheets("Sheet1")
        .Paste .Range("C9")
    End With

End Sub

Hope this helps!

Ok, thaks for help me!

I tried this and ocurr that error message and marks "Worksheets" in blue (Error of compilation. The use of property use is not valid):

For more explication, show the Document that contains the chart.

 

[Domenic]

When you refer to your opened workbook, don't include the path . . .

Workbooks("multiTimeline.xlsx").Worksheets("multiTimeline")...

 

[THEKINGSLG]

Well. I drop the path and apear other error in time execution ( error can't find the element with specific name ):

You can see in past post that the name or workbook, worksheet and chartojb ara these.
Sorry!

 

[Domenic]

Did you check to make sure that the name of the chart that you have specified in your code is spelled correctly, and that there are no extra spaces anywhere?