macro for making chart across sheets


G

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
 
Ad

Advertisements

J

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/
_______
 
Ad

Advertisements

G

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top