Hi Everyone,

In my excel sheet I have a dropdown list that referances different data ranges. I am trying to build a macro that takes a range name from this drop down box and builds a chart with the data. Then if the user wanted to use different data, they can select a new data range name and run the macro again below is my code, but I contiune to get errors:

Any help would be great apprciated! 

Sub trialnow()
'
' trialnow Macro
'
Dim MyRef As String
MyRef = Range("c5").Value
 
Application.Goto Reference:=MyRef


    Charts.Add
    ActiveChart.ApplyCustomType ChartType:=xlUserDefined, TypeName:="Default"
    ActiveChart.SetSourceData Source:=Sheets("Sheet2").Range("I36")
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(1).Values = "Sheet1!MyRef"
    ActiveChart.SeriesCollection(1).Name = "=Sheet2!R5C2"
    ActiveChart.SeriesCollection(2).Values = "=Sheet1!R7C2:R7C67"
    ActiveChart.SeriesCollection(2).Name = "=Sheet2!R6C2"
    ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet2"
    With ActiveChart
        .HasTitle = True
        .ChartTitle.Characters.Text = "ddddd"
        .Axes(xlCategory, xlPrimary).HasTitle = False
        .Axes(xlValue, xlPrimary).HasTitle = True
        .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Millions CDN $"
    End With
    ActiveChart.HasDataTable = False
    ActiveChart.ChartTitle.Select
    Selection.Text = "=Sheet2!R3C2"
End Sub