How to combine two different chart by using VBA coding in Excel?

Question

Set rng = Sheets("Before").Range("B1:B11")
Set rng2 = Sheets("After").Range("B1:B11")

'create chart
Set cht = Sheets("Plot").Shapes.AddChart2

'Give chart some data
cht.Chart.SetSourceData Source:=rng
cht.Chart.SetSourceData Source:=rng2

when I using this two code:

cht.Chart.SetSourceData Source:=rng
cht.Chart.SetSourceData Source:=rng2

the first chart is draw but it is replace by the second chart. How to combine two chart into one diagram?

I already try declare one variable to add the two chart. But it is unsuccessful.

ASH · Accepted Answer · 2022-10-31 13:04:36Z

1

For something like this, I would select different ranges, and plot each in one single chart. See the link below for details.

https://support.microsoft.com/en-us/office/select-data-for-a-chart-5fca57b7-8c52-4e09-979a-631085113862

When that is done, turn on the Macro Recorder and click through all the steps you need. Then the Recorder will generate all the code you want/need. You will inevitable get some superfluous code, but you will also have the exact code that you need to do what you want to do.

answered Oct 31, 2022 at 13:04

ASH

20.5k28 gold badges117 silver badges247 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jon Peltier · Accepted Answer · 2022-11-18 22:05:24Z

1

You need to add the second data source as a new series. SetSourceData takes over the entire chart source data range.

Set rng = Sheets("Before").Range("B1:B11")
Set rng2 = Sheets("After").Range("B1:B11")

'create chart
Set cht = Sheets("Plot").Shapes.AddChart2

'Give chart some data
cht.Chart.SetSourceData Source:=rng

' Add Data
With cht.SeriesCollection.NewSeries
    .Values = rng2
End With

It may be more complicated if cell B1 in each sheet is used for the series names.

answered Nov 18, 2022 at 22:05

Jon Peltier

6,1011 gold badge32 silver badges28 bronze badges

Collectives™ on Stack Overflow

How to combine two different chart by using VBA coding in Excel?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related