Chris -

1) I made the code so it used the entire used range of the active sheet.
To work only on a selected region, change this line

Set rUsed = ActiveSheet.UsedRange

to this

Set rUsed = Selection

2) cht.Name fails when you try to name a sheet with the name of an
existing sheet, or if the cell has illegal characters or too long a
string for a sheet name (limit = 31 characters). Delete that line and
name the charts when you're finished.

3) Control the chart type by inserting this line after Charts.Add:

cht.ChartType = xlXYScatterLines

Actually, once you type the = sign, you get an intellisense dropdown
with all of the available chart types listed.

Another hint. Don't throw away the macro recorder. When your code is
pretty good but you want to change one or two things, turn on the
recorder while playing with a dummy chart, then get the code you need
from the recorded macro.

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


Chris Gregory wrote:

Hi Mr. Peltier,

I am a biology graduate student with a similar problem as rabidchild. As
this is the first time I am attempting to create a macro without the benefit
of "Recording", I'd like to ask you a few questions about your solution:

1) What cells should be highlighted when running this macro? If I highlight
more than one row (other than the column headings), everything is added onto
one chart (different column per row header). I would like it so that there is
a different chart per page & think that maybe I am highlighting something
incorrectly.

2) Are there any parts of your code that need to be replaced by me before
running it ("Placeholders" for variables inherent to my document only)? For
example, I keep getting an error (regardless of what cells I highlight), &
when debugged, Excel refers to the " cht.Name = rVal.Cells(1, 1)" line.

3) Is there a way to switch from a column chart to an x-y scatter plot in
your code?

Most of these questions are probably easy, but I'm not yet sure how to make
my way around the VB code. Eventually I would like to figure out how to
change defaults (i.e. whether or not a legend displays, what view percent is
set) or even utilize dynamic charting (create all my charts once & then have
them all update as I add new species [column headings] or measurements [row
headings]). Small steps first...

Thank you (or anyone else) for your response,

Chris