Named Ranges for Chart

[Sami82]

Hi All,

I'm trying to write a macro to produce a chart where the data change
dependant on what day is picked. So far it is only pulling up th
original day where the named range in the sheet points to. I have th
following code to run which should change the range:


Code
-------------------

ActiveAddress = ActiveCell.Offset(3, 0).Address
ActiveColumn = Left(ActiveAddress, 2)
ActiveSRange = ActiveAddress & ":" & ActiveColumn
With Worksheets("Category")
Set CVRange = .Range(ActiveSRange & .Cells(.Rows.Count, "A").End(xlUp).Row)
Set CTRange = .Range("B10:B" & .Cells(.Rows.Count, "A").End(xlUp).Row)
End With

Sheets("tmpChart").Activate
Set Cht = Charts.Add
Set Cht = Cht.Location(Where:=xlLocationAsObject, Name:="tmpChart")
With Cht
.ChartType = xlPie
.SetSourceData Source:=Sheets("Category").Range("CVRange"), _
PlotBy:=xlColumns
.SeriesCollection(1).XValues = CTRange
.HasTitle = True
.ChartTitle.Characters.Text = "Category Chart - " & ChartDay
With .Parent
.Top = Range("B7").Top
.Left = Range("B7").Left
.Name = "DayChart"
End With
End With

 

[Sami82]

Issue with new code:

I figured out how to make it adjust with the following code but i now
get an error 91 on the CTitleRange = CTRange.Address(external:=True)
line. Can anyone help me work out why. I'm doing the same thing with
both lines


Code:
--------------------

With Worksheets("Category")
Set CVRange = .Range(ActiveSRange & .Cells(.Rows.Count, "A").End(xlUp).Row)
CValueRange = CVRange.Address(external:=True)
Set CTRange = .Range("B10:B" & .Cells(.Rows.Count, "A").End(xlUp).Row)
CTitleRange = CTRange.Address(external:=True)
End With