Macro for Graph - problem with Range

[Tanya]

Could someone please help me with the following code

Because my list changes with new entries I need the macro to identify the
correct range. I did try substituting Range("B10:G13")
with .ActiveCell and all combinations I have tried receive the following error

Run-time error '438'
Object doesn't support this property or method

Is there another line I need to add to this code for it to recognise the end
of the data table?

Sub Macro9()
'
' Macro9 Macro
' Macro recorded 3/03/2007 by Tanya
'
'
Range("Header").Select
Charts.Add
ActiveChart.ChartType = xlLineMarkersStacked
'Difficulty with next line of code - problem with Range as area changes with
new entries in list
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("B10:G13"),
PlotBy _
:=xlRows
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Daily Hours Worked"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Day of Week"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Hours worked"
End With
With ActiveChart
.HasAxis(xlCategory, xlPrimary) = True
.HasAxis(xlValue, xlPrimary) = True
End With
ActiveChart.Axes(xlCategory, xlPrimary).CategoryType = xlAutomatic
With ActiveChart.Axes(xlCategory)
.HasMajorGridlines = False
.HasMinorGridlines = False
End With
With ActiveChart.Axes(xlValue)
.HasMajorGridlines = True
.HasMinorGridlines = False
End With
ActiveChart.HasDataTable = False


End Sub

 

[Jay]

Add these two lines before Range("Header").select:
Dim chartrange As Range
Set chartrange = Range(Range("B10"), Range("G10").End(xlDown))

Then, adjust your problem statement with the following line:
ActiveChart.SetSourceData Source:=chartrange, PlotBy:=xlRows

 

[Tanya]

Thank you very much Jay, it worked a charm.

Kind Regards
Tanya