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