Automatically Create Graphs

[rabidchild]

Hi

I have a worksheet with about 150 rows and 50 columns. I would like t
create a bar chart for each rows worth of data on a seperate worksheet
using the column headers as the labels on the x axis and the cel
contents as the y axis (with the first cell in each row as the char
title).

Is there a way of automatically generating all these graphs? I a
currently creating them individually.

Thanks

R

 

[Jon Peltier]

If all that exists on the worksheet is your data, we can write code that
plots each row of the used range, as this macro does:

Sub OneChartPerRow()
Dim rCat As Range
Dim rVal As Range
Dim rUsed As Range
Dim iRow As Long
Dim cht As Chart

Set rUsed = ActiveSheet.UsedRange
Set rCat = rUsed.Rows(1)

For iRow = 2 To rUsed.Rows.Count
Set rVal = rUsed.Rows(iRow)
Set cht = Charts.Add
cht.Name = rVal.Cells(1, 1)
With cht
.SetSourceData Source:=Union(rCat, rVal)
.HasTitle = False
.HasTitle = True
With .ChartTitle
.Text = rVal.Cells(1, 1)
End With
End With
Next
End Sub


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

 

[Ed Ferrero]

Hi rabidchild,

One other idea is to generate each chart as a .gif file and save them in a
folder. From there you can print the charts, or link them in a report. This
stops the workbook from getting too big (150 chart sheets is a lot to
handle).

There is a sample workbook 'Multiple Chart Builder' at
http://edferrero.m6.net/charting.aspx that does this.

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

 

[Jon Peltier]

Or you could copy a picture of each chart (as picture, not as bitmap)
and paste the picture in the workbook. This prevents resource problems,
but retains the scalability of the chart.

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

 

[Chris Gregory]

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

 

[Jon Peltier]

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