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

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...

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

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("D1:D100")
' Adjust ranges as required
End With
Next
End Sub

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


wrote in message
oups.com...
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...

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

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!!!



Jon Peltier wrote:
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("D1:D100")
' Adjust ranges as required
End With
Next
End Sub

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


wrote in message
oups.com...
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...