Change Chart Data Source To Different Column Ranges Using VBA

[rafal1122]

Hi

I spent last day or so trying to create a macro that would update the data source of the series in the chart. I went thorugh dozens of solutions which I found on the net and none of them are doing what I need them to do.

The problem is that the series of data on the chart pull data from different columns of the data source table.

This is what I get when I update the Data Source and record the macro:

ActiveChart.SetSourceData Source:=Sheets(1).Range("L12:L65,N12:N65")

what I would like to replace it with is the following:

Dim MyDataSource1 as Range
Dim MyDataSource2 as Range


LastCellColumnL = ....
LastCellColumnN = ...
Set MyDataSource1 = ActiveSheets.Range(L12, LastCellColumnL)
Set MyDataSource2 = ActiveSheets.Range(N12, LastCellColumnN)

ActiveChart.SetSourceData Source:=Sheets(1).Range(MyDataSource1,MyDataSource2)

The problem is that why I do this, it does not treat it the same way as the original line, it also includes all the columns in between the two data ranges.
How to make

ActiveChart.SetSourceData Source:=Sheets(1).Range(MyDataSource1,MyDataSource2)

to work like:

ActiveChart.SetSourceData Source:=Sheets(1).Range("L12:L65,N12:N65")

and only include the two columns like in the original line here?

Rafal

[Admin]

Hi Rafal,

Welcome to ExcelFox!!

PLease use code tags. This time I added for you.

Try

ActiveChart.SetSourceData Source:=Union(MyDataSource1, MyDataSource2)

[rafal1122]

Hi Rafal,

Welcome to ExcelFox!!

PLease use code tags. This time I added for you.

Try

ActiveChart.SetSourceData Source:=Union(MyDataSource1, MyDataSource2)

It works, thank you so much.

Also, I have noticed that updating the data source changes axis lables to default values (1,2,3,4...)
I had to implement additional piece of code:

DestBook.Worksheets(4).ChartObjects("Chart 61").Chart.SeriesCollection(1).XValues = MyDataSource3