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.

 

[THEKINGSLG]

¿Cuál crees que es el nombre correcto del gráfico?
1) Categoría: Todas las categorías MATIC: (Todo el mundo)
2) Categoría: Todas las categorías
3) CategorÃa: Todas las categorÃas
4) CategorÃa: Todas las categorÃas MATIC: (Todo el mundo)

Other two (workbook and worksheet) are corrects.

Then, i will insert the copied chart in this document (Simple_CSV_Plotter.xlsm).

 

[Domenic]

I am not at my computer, but in order to find your charts name, first manually select the chart, and then look at the name box, which you will find at the upper left area of excel, to the left of the formula bar.

Does this help?

 

[THEKINGSLG]

I just check that and the real name is "Gráfica 1".

I execute and show the next error in time execution (object required):

I´ve try eliminate the "with" with this with success.

Worksheets("Sheet1").Range("C9").Select
Worksheets("Sheet1").Paste

Thanks for help me Domenic

 

[Domenic]

.Paste and .Range("C9") should be on the next line between With Worksheets("Sheet1") and End With. Also that there should be a space between .Paste and .Range("C9"). So it should be as follows . . .

    With Worksheets("Sheet1")
        .Paste .Range("C9")
    End With

 

[THEKINGSLG]

Ok! I use it. Ty.

I am trying to change the size of the chart before doing "Copy-Paste Macro" in this with when create a "" document to insert the chart. But the "Resize chart" pass cannot do.

' 5. Insert and populate chart
With ws.Shapes.AddChart.Chart '' Excel 2007+
''With ws.Shapes.AddChart2.Chart '' Excel 2013 only

'' chart type
.ChartType = xlLineMarkers

'' chart data
.SetSourceData Source:=ws.UsedRange

'' Resize chart
¿?

End With

 

[THEKINGSLG]

I´ve can to do this:

ActiveSheet.ChartObjects("Gráfico 1").Activate
With ActiveSheet.Shapes("Gráfico 1")
.Width = 750
.Height = 215
.Left = 0
End With

 

[Domenic]

It looks like you've figured out how to resize the chart. Although, just the following would suffice . . .

With ActiveSheet.ChartObjects("Gráfico 1")
    .Width = 750
    .Height = 215
    .Left = 0
End With

 

[THEKINGSLG]

Yeah, thaks!

I´ve applied all code in the real document and path, put the CSV in correct path, but have a small problem that sure you can guess. (Subindex out if interval)

 

[Domenic]

Your multiTimeline.xlsx workbook must be open. Is it open?

 

[THEKINGSLG]

Is not necesary open it. My fail was that i was exacuting a other down sub.

Im trying to set a name of the picture pasted but before deleting the old picture inserted (name "MATIC").

Sub Copy_Paste_Chart_xlsx()

' 7. Select chart of new file
Workbooks("multiTimeline.xlsx").Worksheets("multiTimeline").ChartObjects("Gráfico 1").CopyPicture xlScreen, xlPicture

With Worksheets("ENLACES INTERES")
.Paste .Range("C11")
End With

End Sub

¿Some sugestion of two problems?