Adaptive Chart Data Range

[schwiggiddy]

Hey all, I've been puzzling over this problem for a little while and I've not made much progress with it. I'm hoping the collective has some insights.

I have a field of data on a worksheet called "summary." Weekly dates saved as text are on the left side; they're saved as text for the purpose of INDIRECT functions in the data referring to other worksheets named similarly. The "headers" of the columns are pasted via a macro from the most recent worksheet. The data then uses the pasted headers to VLOOKUP specific data on the other worksheets.

The chart exists on another tab. It's source data is the data on the summary worksheet; however, the columns and rows are swapped so the series are by dates and the data pasted from the most recently dated worksheet are along the x-axis.

I'm trying to get this to the point that someone with extremely limited excel knowledge can fill in data into a dated worksheet and run the macro that ultimately updates the chart to show the 4 most recent weeks of data and only for the number of columns pasted, which varies week to week. I've gotten it to that point right up until you change the data range for the chart. It's as easy without code as changing the rows in the "Chart data range" field on the "Select Data Source" box, but how does one do that with VBA?

 

[Worf]

Hey

The example below uses a RefEdit control inside a form. Use it to select the source data, close the user form and the chart will update.

You may also like this: https://peltiertech.com/refedit-control-alternative/

' UserForm module
Private Sub RefEdit1_Exit(ByVal Cancel As MSForms.ReturnBoolean)


Sheets("Sheet1").ChartObjects("Chart 1").Chart.SetSourceData _
Range(Split(Me.RefEdit1, "!")(1))


End Sub