Hi there. I want to create a graph for the title row and then each row
in turn of a spreadsheet, choosing the next rwo down each time.
Trouble is there are 150 rows and I want to write a macro to do it for
me. I have tried with a modicum of success. I can create a graph for
each row in a new chart and rename it, but it produces the same graph
each time, as I cannot get the "RowSource" control to accept a
variable. This is my code:
Sub Create_Graph()
'
' Create_Graph Macro

Dim Nextrow As Integer
Dim Titlerow As Range
Dim j As Range
Dim k As Range
Dim l As Range
Dim m As Range
Dim n As Range
Dim o As Range
Dim p As Range
Dim q As Range
Dim r As Range
Dim s As Range
Dim t As Range
Dim u As Range

For Nextrow = 2 To 5

Set j = Worksheets("All Year").Range("J" & Nextrow)
Set k = Worksheets("All Year").Range("K" & Nextrow)
Set l = Worksheets("All Year").Range("L" & Nextrow)
Set m = Worksheets("All Year").Range("M" & Nextrow)
Set n = Worksheets("All Year").Range("N" & Nextrow)
Set o = Worksheets("All Year").Range("O" & Nextrow)
Set p = Worksheets("All Year").Range("P" & Nextrow)
Set q = Worksheets("All Year").Range("Q" & Nextrow)
Set r = Worksheets("All Year").Range("R" & Nextrow)
Set s = Worksheets("All Year").Range("S" & Nextrow)
Set t = Worksheets("All Year").Range("T" & Nextrow)
Set u = Worksheets("All Year").Range("U" & Nextrow)

Set Titlerow = Worksheets("All Year").Range("J1:U1")
Set myMultipleRange = Union(Titlerow, j, k, l, m, n, o, p, q, r,
s, t, u)


Worksheets("All Year").Select
myMultipleRange.Select
j.Activate

Charts.Add
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Sheets("All
Year").Range(myMultipleRange), PlotBy _
:=xlRows
ActiveChart.Location Whe=xlLocationAsNewSheet
ActiveSheet.Name = "Fred"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Fred"
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With
ActiveChart.Legend.Select
Selection.Delete

Sheets("All Year").Select
Forename = Range("G" & Nextrow).Text
Surname = Range("H" & Nextrow).Text

Sheets("Fred").Select
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = Forename & " " & Surname
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With
ActiveSheet.Name = Forename & " " & Surname
Sheets("All Year").Select
Next Nextrow
End Sub

Can anybody help me?

Regards

Don

on the .range use soething like this to get your offsets

..Range("A1").Offset(RowOffset:=Nextrow, columnoffset:=0)


Note: Nextrow is an offset from the Range "A1"



"Don" wrote:

Hi there. I want to create a graph for the title row and then each row
in turn of a spreadsheet, choosing the next rwo down each time.
Trouble is there are 150 rows and I want to write a macro to do it for
me. I have tried with a modicum of success. I can create a graph for
each row in a new chart and rename it, but it produces the same graph
each time, as I cannot get the "RowSource" control to accept a
variable. This is my code:
Sub Create_Graph()
'
' Create_Graph Macro

Dim Nextrow As Integer
Dim Titlerow As Range
Dim j As Range
Dim k As Range
Dim l As Range
Dim m As Range
Dim n As Range
Dim o As Range
Dim p As Range
Dim q As Range
Dim r As Range
Dim s As Range
Dim t As Range
Dim u As Range

For Nextrow = 2 To 5

Set j = Worksheets("All Year").Range("J" & Nextrow)
Set k = Worksheets("All Year").Range("K" & Nextrow)
Set l = Worksheets("All Year").Range("L" & Nextrow)
Set m = Worksheets("All Year").Range("M" & Nextrow)
Set n = Worksheets("All Year").Range("N" & Nextrow)
Set o = Worksheets("All Year").Range("O" & Nextrow)
Set p = Worksheets("All Year").Range("P" & Nextrow)
Set q = Worksheets("All Year").Range("Q" & Nextrow)
Set r = Worksheets("All Year").Range("R" & Nextrow)
Set s = Worksheets("All Year").Range("S" & Nextrow)
Set t = Worksheets("All Year").Range("T" & Nextrow)
Set u = Worksheets("All Year").Range("U" & Nextrow)

Set Titlerow = Worksheets("All Year").Range("J1:U1")
Set myMultipleRange = Union(Titlerow, j, k, l, m, n, o, p, q, r,
s, t, u)


Worksheets("All Year").Select
myMultipleRange.Select
j.Activate

Charts.Add
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Sheets("All
Year").Range(myMultipleRange), PlotBy _
:=xlRows
ActiveChart.Location Whe=xlLocationAsNewSheet
ActiveSheet.Name = "Fred"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Fred"
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With
ActiveChart.Legend.Select
Selection.Delete

Sheets("All Year").Select
Forename = Range("G" & Nextrow).Text
Surname = Range("H" & Nextrow).Text

Sheets("Fred").Select
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = Forename & " " & Surname
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With
ActiveSheet.Name = Forename & " " & Surname
Sheets("All Year").Select
Next Nextrow
End Sub

Can anybody help me?

Regards

Don