Need Help creating multiple graphs through coding

[canes_26]

Greetings-

I have a spreadsheet that has roughly 4500 rows with 7 columns of data
Along each row I need to make a line graph that uses the values fro
the seven columns in that row.

Seeing as though creating each chart one by one would take FOREVER
was wondering if there is a way to write a program that will do thi
for me?

I purchased a book called "Writing Excel Macros" by Steven Roman. Bu
I am still having trouble finding anything that will help. If someon
could help me, or direct me where to go it would be very muc
appreciated.

Thank you

 

[Tim Williams]

What is the purpose of creating all those graphs? Do you really need
to create them all at the same time or could you just create a block
at a time?

In the past when creating large numbers of plots from the same
datablock I've found it works well to create a graph, copy a picture
of it and paste it into another sheet, shift the graph data source to
the next row, copy picture etc etc.

If you need to get a start you might try recording a macro while
creating the first graph and use that as a basis. Be warned that 4500
plots is a large number for one spreadsheet so you may not be able to
create them all in one batch.

You can contact me via email if you are really stuck and I'll try
digging out some previous code...


Tim.

 

[Wouter]

Hi,

Assuming there is a unique value in you first column:

Option Explicit

Sub MakeGraphs()
Dim lRow As Long
Dim sSheet As String
Dim sGraph As String
Dim sSource As String
'
Application.ScreenUpdating = False
sSheet = ActiveSheet.Name
For lRow = 2 To ActiveSheet.UsedRange.Rows.Count
sSource = "A1:G1,A" & CStr(lRow) & ":G" & CStr(lRow)
Range(sSource).Select
Range("A" & CStr(lRow)).Activate
sGraph = ActiveCell.Value
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData _
Source:=Sheets(sSheet).Range(sSource), _
PlotBy:=xlRows
ActiveChart.Location Where:=xlLocationAsNewSheet

With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = sGraph
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With
ActiveSheet.Name = sGraph
Sheets(sSheet).Activate
Next
Application.ScreenUpdating = True
End Sub

Good luck,


Wouter HM.