Inserting Variables into Excel VBA script

Hey all

What I want to be able to do is effectivly an INDIRECT function in VBA, In the following Code where it says ("AM6:AM9") I am wanting it to replace these whith the values found in those cells each time the macro is run, the values are cell references.
When I create a chart I use =INDIRECT in the Chart cell reference to get the correct data (see attached image) but the macro only records the data that was in the cells at the time it was recorded. I am wanting the macro to recheck the indirect cells each time it is run.

Charts.Add
    ActiveChart.ChartType = xlColumnClustered
    ActiveChart.SetSourceData Source:=Sheets("sheet1").Range("AM6:AM9"), _
        PlotBy:=xlRows
    ActiveChart.Location Where:=xlLocationAsObject, Name:="sheet1"
    With ActiveChart
        .HasTitle = False
        .Axes(xlCategory, xlPrimary).HasTitle = False
        .Axes(xlValue, xlPrimary).HasTitle = False
    End With
End Sub

The reason I want to do this is so that the user can choose some dates in a dropdown and then run this macro to generate a chart based on data from the time period they specified.

Am I making any sense?

Any help would be appreciated