Create Charts with a Macro

[DanExcel]

Is it possible to create charts with a macro? I have sets of "x" and "y" data on over 100 worksheets and I'd like to quickly make an x-y scatter plot for every single worksheet.

Also, i dont know if it makes a difference, but the number of points is not the same on every worksheet.

Thanks!

[Mark O'Brien]

It is possible to add charts using VBA. (and very difficult to mess about with if you record a macro to see what the code is.)

To embed a scatter chart on Sheet1 and use B2:C11 as the source data, you would use code like this:

  Dim Cht As Chart

  

  Set Cht = Application.Charts.Add

  With Cht

    .ChartType = xlXYScatter

    .SetSourceData Source:=Sheets("Sheet1").Range("B2:C11"), PlotBy:=xlColumns

    .Location Where:=xlLocationAsObject, Name:="Sheet1"

  End With

[DanExcel]

Mark,

Thanks alot. This is what I am looking for.

Do you (or does anyone) know how I can change the following two parts:

- How can I make it refer to x and y values that are in non-adjacent columns? (for the "Range" part)

- Is there a way to make it dynamically plot however many points there are (for example, some sheets have 10 points, some have 20, some have hundreds... they are all in the same columns just on different sheets).

Please let me know if this is unclear. Thank you all