Hello,
I currently have a spreadsheet that automatically calculates data and populates a 12 month chart. However, I do not want to delete the previous months data that was generated, but I am having trouble selecting a dynamic range with static legend data. I want all of the data from previous months to be available, while changing the data to the current 12 month period. I would like to do this by adding the new data to the bottom, and selecting the data from the previous 12 months using an offset.
Any help would be greatly appreciated!
My current code that comes close to selecting the correct data is as follows:
Sub Charting()
Dim ChartRange As Range
Set ws = ActiveSheet
Set rng = ws.Columns("K").Find("*", ws.[K1], xlValues, , xlByRows, xlPrevious)
'Finding bottom of the data
rng.Offset(1, 1).Select
'12 month data selection
Set ChartRange = Range(Selection, Selection.Offset(-35, -11))
ActiveSheet.ChartObjects("Chart 2").Activate
'This is where I am having issues, because it selects all of the data, instead of the static legend and the dynamic range
ActiveChart.SetSourceData Source:=Range("$A$1:$L$1", ChartRange), PlotBy:=xlColumns
End Sub
I currently have a spreadsheet that automatically calculates data and populates a 12 month chart. However, I do not want to delete the previous months data that was generated, but I am having trouble selecting a dynamic range with static legend data. I want all of the data from previous months to be available, while changing the data to the current 12 month period. I would like to do this by adding the new data to the bottom, and selecting the data from the previous 12 months using an offset.
Any help would be greatly appreciated!
My current code that comes close to selecting the correct data is as follows:
Sub Charting()
Dim ChartRange As Range
Set ws = ActiveSheet
Set rng = ws.Columns("K").Find("*", ws.[K1], xlValues, , xlByRows, xlPrevious)
'Finding bottom of the data
rng.Offset(1, 1).Select
'12 month data selection
Set ChartRange = Range(Selection, Selection.Offset(-35, -11))
ActiveSheet.ChartObjects("Chart 2").Activate
'This is where I am having issues, because it selects all of the data, instead of the static legend and the dynamic range
ActiveChart.SetSourceData Source:=Range("$A$1:$L$1", ChartRange), PlotBy:=xlColumns
End Sub
Last edited:
