plot graph from multiple worksheet as embedded chart object on every worksheet

[Jeffrey.Tiong]

Hi i want to plot the graph for every worksheet (column B and column
D). I have more than 50 worksheets in the same workbook. How do i write
a macro to ask the excel to plot the graph for every sheet and present
the chart object on the same sheet? thank you very much for your help!
i have been working on this over 2 days still cannot figure it out...

 

[Jon Peltier]

Jeff -

It goes something like this:

Sub ChartOnEveryWorksheet()
Dim ws As Worksheet
Dim cht As Chart

For Each ws In ActiveWorkbook.Worksheets
Set cht = ws.ChartObjects.Add(100,100,350,275).Chart
' adjust arguments above to get preferred chart position in sheet
cht.ChartType = xlXYScatter
' adapt to your preferred chart type
With cht.NewSeries
.Name = "Whatever"
.XValues = ws.Range("B1:B100")
.Values = ws.Range("D1100")
' Adjust ranges as required
End With
Next
End Sub

- Jon

 

[jeftiong]

Hi Jon,

there is a an error on this line of code

ActiveChart.SetSourceData
Source:=Sheets("myworkbooks").Range("A5,C10" _
), PlotBy:=xlColumns

this is not the first time, i tried to write the code myself, but
everytime my laptop has this error

"Runtime error '9', subscript out of range"

you know how to solve this? this has been bugging me for past few
days!!

thanks!!!

 

[Andy Pope]

Hi,

Do you have a worksheet called myworkbooks?

Cheers
Andy