Automatically Create Graphs

Hi

I have a worksheet with about 150 rows and 50 columns. I would like to 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 cell contents as the y axis (with the first cell in each row as the chart title).

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

Thanks

RC

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


rabidchild wrote:

> Hi
>
> I have a worksheet with about 150 rows and 50 columns. I would like to
> 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 cell
> contents as the y axis (with the first cell in each row as the chart
> title).
>
> Is there a way of automatically generating all these graphs? I am
> currently creating them individually.
>
> Thanks
>
> RC
>
>

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/

>
> Hi
>
> I have a worksheet with about 150 rows and 50 columns. I would like to
> 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 cell
> contents as the y axis (with the first cell in each row as the chart
> title).
>
> Is there a way of automatically generating all these graphs? I am
> currently creating them individually.
>
> Thanks
>
> RC
>
>
> --
> rabidchild
> ------------------------------------------------------------------------
> rabidchild's Profile:
> http://www.excelforum.com/member.php...fo&userid=2627
> View this thread: http://www.excelforum.com/showthread...hreadid=401451
>

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


Ed Ferrero wrote:

> 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/
>
>
>>Hi
>>
>>I have a worksheet with about 150 rows and 50 columns. I would like to
>>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 cell
>>contents as the y axis (with the first cell in each row as the chart
>>title).
>>
>>Is there a way of automatically generating all these graphs? I am
>>currently creating them individually.
>>
>>Thanks
>>
>>RC
>>
>>
>>--
>>rabidchild
>>------------------------------------------------------------------------
>>rabidchild's Profile:
>>http://www.excelforum.com/member.php...fo&userid=2627
>>View this thread: http://www.excelforum.com/showthread...hreadid=401451
>>
>
>
>

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

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
>

So far this macro is working well for my purposes. The only issue I am having is that the first column (column A - which I want to be my chart title) is also part of the chart as a separate bar. Is there a way to make Column A be only the title and Columns B-E be the data?

Hallo,

Even I have a similar problem.

I would like auto generate the Worksheets according to my data in "B23".

Later, after generation of worksheets - I need to auto generate the chart for the data related to each worksheets.

Thanks in advance.
cadcraft

Hallo, I am unable to start new thread.

Mr. Peltier,

How would you adjust your code to add a second series to each chart? I would like to have both series on the same row if possible.

For example, I have cost and value data that I would like to show as two series. Cost is in B2-AP2 and Value is AQ2-CE2

Thanks!

Originally Posted by 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/
_______


rabidchild wrote:

> Hi
>
> I have a worksheet with about 150 rows and 50 columns. I would like to
> 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 cell
> contents as the y axis (with the first cell in each row as the chart
> title).
>
> Is there a way of automatically generating all these graphs? I am
> currently creating them individually.
>
> Thanks
>
> RC
>
>