Thread: How to Automatically Create Scatter Plot Charts

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

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/

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

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

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

>>>Hi Ed,
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

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

Chris

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

"Ed Ferrero" wrote:

> 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
>
> >>>Hi Ed,
> 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
>
>
>

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

"TPL." wrote:

> 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
>
> "Ed Ferrero" wrote:
>
> > 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
> >
> > >>>Hi Ed,
> > 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
> >
> >
> >