macro for making chart across sheets

[Guest]

hi!
I have tons of data on different worksheets and I tried to create a macro
to plot a chart for each set of data
But the macro will only work on the original data. Every time I run the
macro on a new worksheet, it plots the original data. How can I fix this?


Thanks


Minhao Wong


Range("A7:B407").Select
Charts.Add
ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A7:B407"), _
PlotBy:=xlColumns
ActiveChart.Location Where:=xlLocationAsNewSheet
With ActiveChart
.HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text =
"wavelength/nm"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "absorbance"
End With
End Sub

 

[Jon Peltier]

Your macro tells Excel to plot the data in Sheet1!A7:B407. You have to write some
kind of algorithms to choose the proper workbook, worksheet, and cells for each chart.

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

 

[Guest]

Hi Jon
Thank you for your reply
How do i write such an algorithm? any online guide? I can't seem to find any
that can extend the macro to all sheets.

Minhao