How to Automatically Create Scatter Plot Charts

[Chris Gregory]

Hello,

I am a biology graduate student who is taking many measurements of animals
(height, weight, tail length, etc. & don't worry - they all survive). I have
a spreadsheet set up with ~200 rows [the measurement as described above] and
~100 columns [representing the various animals I have caught and measured]. I
would like to create an XY (Scatter) chart for each row of data on seperate
worksheets, using column headers as x-axis labels, and the row header as both
the y-axis and chart title label. Of lesser importance is to turn off the
legend (the chart title says enough about the graph) and to have the zoom set
at 75% (to be able to see the entire chart all at once - my screen loses the
edges of charts set at 100%). Eventually I would like to figure out how to
utilize dynamic charting (automatic updating of charts) with this as I will
constantly add new species and measurements.

Is there a way of automatically generating all these graphs? I am currently
creating them individually, but would rather highlight the row(s) of
interest, then have the charts created for me. And as I will more than likely
need to start learning VBA to create this stuff for myself, does anyone have
recommendations for a particularly good, easy to understand beginner's guide
to VBA?

Thank you for your responses,

Chris

 

[Ed Ferrero]

HI Chris,

Have a look at http://edferrero.m6.net/charting.aspx and see if any of the
samples there help you.

In particular, 'Chart Selector' and 'Multiple Chart Builder' might do what
you want. Currently configured as a line chart, but you can easily change
the chart type to suit yourself.

Ed Ferrero
http://edferrero.m6.net/

 

[Ed Ferrero]

Hi again,

I have now modified 'Multiple Chart Builder' slightly so that you can send
the charts to separate chart sheets within the workbook as well as sending
them to gif files.

I would take a backup of the data before trying this with 200 charts.

Ed Ferrero
http://edferrero.m6.net/

Have a look at http://edferrero.m6.net/charting.aspx and see if any of
the samples there help you.

In particular, 'Chart Selector' and 'Multiple Chart Builder' might do what
you want. Currently configured as a line chart, but you can easily change
the chart type to suit yourself.

Ed Ferrero
http://edferrero.m6.net/

Hello,

I am a biology graduate student who is taking many measurements of
animals
(height, weight, tail length, etc. & don't worry - they all survive). I
have
a spreadsheet set up with ~200 rows [the measurement as described above]
and
~100 columns [representing the various animals I have caught and
measured]. I
would like to create an XY (Scatter) chart for each row of data on
seperate
worksheets, using column headers as x-axis labels, and the row header as
both
the y-axis and chart title label. Of lesser importance is to turn off the
legend (the chart title says enough about the graph) and to have the zoom
set
at 75% (to be able to see the entire chart all at once - my screen loses
the
edges of charts set at 100%). Eventually I would like to figure out how
to
utilize dynamic charting (automatic updating of charts) with this as I
will
constantly add new species and measurements.

Is there a way of automatically generating all these graphs? I am
currently
creating them individually, but would rather highlight the row(s) of
interest, then have the charts created for me. And as I will more than
likely
need to start learning VBA to create this stuff for myself, does anyone
have
recommendations for a particularly good, easy to understand beginner's
guide
to VBA?

Thank you for your responses,

Chris

 

[Ed Ferrero]

Hi Chris,
I will answer in reverse order;
3) The worksheet is already based on dynamic ranges. If you add months or
data rows the range will extend automatically. Look at menu item <Insert -
Name - Define> to see the xAxis named range and see how this is defined
dynamically.
2) There are two charts on Sheet1. Click on a cell in row 9 (containing
months) to show the hidden month chart, click on a blue shaded cell in
column A to show the row chart. To change the default chart, show the row
chart, then use the chart menu ,Chart - Chart Type> to change the chart to
an XY Scatter. Save the workbook. Done.
1) The code BuildChartSheets needs the following procedures to run;
ShowCht
SetChartSeries
FirstBlue

Ed Ferrero
http://edferrero.m6.net
Thank you very much for your link. I found your page before I posted but
didn't think about the Multiple Chart Builder because of the gif-only
capabilities (at the time). Anyway, like the VBA macro & have some follow
questions (if you have the time):
1) To have the charts appear on unique sheets, does the Build as Unique
Sheets macro only need the code at the bottom of the email?
2) If so (or not), is there a way to somehow change the VBA code so that the
default chart type is XY Scatter instead of Column?
3) Looking ahead, would there be a way to dynamically name a range in the
VBA code, so if I added new months, the charts would automatically update?
This question is more out of curiosity as I would have no problem deleting
the old tables & re-running the macro to get the result I need. Still saves
a lot of time...
Thank you again for your time and help,
Chris
----
This part for sure?
Sub BuildChartSheets()
Dim cell As Range
Dim sPath As String
Dim sName As String
Dim sSheet As String
Application.ScreenUpdating = False
With ActiveSheet
sSheet = .Name
For Each cell In Intersect(.Range("A:A"), .UsedRange)
If cell.Font.Bold = True And cell.Interior.ColorIndex = 15 Then
Call ShowCht(ActiveSheet, cell)
sName = cell.Value
..Shapes("chtDim").Copy
..Paste
' ActiveChart.Paste
ActiveChart.Location Where:=xlLocationAsNewSheet, Name:=sName
Worksheets(sSheet).Activate
End If
Next
End With
End Sub
--------
And maybe this part?
Function FirstBlue(tgt As Range) As Range ' find the first blue shaded cell
above tgt
Set FirstBlue = tgt
Do Until FirstBlue.Interior.ColorIndex = 37
Set FirstBlue = FirstBlue.Offset(-1, 0)
If FirstBlue.Row = 1 Then Exit Do
Loop
End Function

 

[Chris Gregory]

For anyone needing to do what I did, Ed's MCB is perfect & I recommend
trying it out. Thank you for your help!

Chris

 

[TPL.]

Hello, I found this topic since I am also wanting to create many charts from
my data. The Multiple Chart Builder is fantastic and will do the job
perfectly. I have one question you will be able to help me with. I am trying
to copy and paste data into the worksheet but it does not allow me to paste.
How can I get around this without having to manually input all the data? (I
have about 700 rows).

Thanks in advance

Tony Lees

 

[TPL.]

Just to let you know I eventually worked it out. Thanks anyway. Tony

 

[SteveC]

Ed Ferrero's site is down. Any one know where I can get this "Multiple Chart
Builder" he created? It sounds very useful. Thanks.

 

[Jon Peltier]

Not too long ago, Ed got his own domain:

http://www.edferrero.com/Charting.aspx

- Jon