Change Chart Series dynamically VBA

Question

I am trying to achieve dynamically chart Time Series

To do this I am copying Dates and Profit from Data Orders from my raw data to another sheet where I will use it to dynamically create chart.

But it is giving me an error. near the line where I am creating range, is there a better way to do this ?

Sub ChartDataSeries()

    Dim ws As Worksheet
    Dim ws1 As Worksheet
    Dim rng As Range

    Set ws = Sheets("Data - Orders")
    Set ws1 = Sheets("Sheet10")
    ws.Range("C:C").Copy 'Source Copy Order Date
    ws1.Activate
    ws1.Range("B1").Select 'Paste Order Date
    ActiveSheet.Paste

    ws.Range("I:I").Copy 'Copy Profit Series
    ws1.Activate
    ws1.Range("C1").Select
    ActiveSheet.Paste ' Paste Profit Series
    ws1.Range("B1").Select
    Set rng = Range(Selection, Selection.End(xlToRight)).Select ' Error Ocurring here
    ActiveSheet.ChartObjects("Chart 7").Activate
    ActiveChart.SetSourceData Source: rng.Select
    'ActiveChart.SeriesCollection(1).XValues = Range("Order Date")
    'ActiveChart.SeriesCollection(1).Values = Range("Profit")



End Sub

Shai Rado · Accepted Answer · 2017-08-02 08:27:00Z

2

It's better if you stay away from all this Activate, Select, Selection, ActiveChart, etc..

Replace the following 4 lines:

ws1.Range("B1").Select
Set rng = Range(Selection, Selection.End(xlToRight)).Select ' Error Ocurring here
ActiveSheet.ChartObjects("Chart 7").Activate
ActiveChart.SetSourceData Source: rng.Select

With these 2:

Set Rng = ws1.Range(ws1.Range("B1"), ws1.Range("B1").End(xlToRight))
ActiveSheet.ChartObjects("Chart 7").Chart.SetSourceData Source:=Rng

answered Aug 2, 2017 at 8:27

Shai Rado

33.7k7 gold badges32 silver badges56 bronze badges

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

3 Comments

Shai Rado Over a year ago

@avabhishiek you can't Set a Range object, and Select it at the same line. You need 1 line to Set, and you realy don't need to Select it to use it as a Chart's SetSourceData

Shai Rado Over a year ago

@avabhishiek please mark this as "ASNWER", you have many post lately you don't five credit to people who helped you

av abhishiek Over a year ago

sorry about that , I have marked it as answer , thanks for the help

Collectives™ on Stack Overflow

Change Chart Series dynamically VBA

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related