Dynamic Excel Chart: Pulling Data from Two Different Tables (VBA?)

Hi All,

I'm having some trouble in trying to create a dynamic chart that pulls data from two different tables based upon the value of a combobox. So far, i've had to create each chart manually, name it, and call it via vba based on the value of a cell - this is very time consuming, as i have many charts..

I would like to know how to change the series of the created chart based upon the value of the combobox, and pull data from the appropriate table using vba. I've attached an example worksheet to give you a better understanding of my problem, and the VBA code and method i've used to date.

When posting a solution, please use comments in your code - really trying my best to learn this stuff!

Thanks everyone!

Best,
Chris

Hey thanks for the reply - ALMOST got it.

For your example, the X axis does not change.

For the "Maximum Value" chart - i'd like the x-axis to be the following: Option 1, Option 2, Option 3, Option 4, Option 5
For everything else, the x-axis should be : COA1, COA2, COA3, COA4, COA 5

Oh nevermind, just modified the code a little to reference the values...

Sub DynamicChart()
    
    Select Case Range("O4")
    
    Case Is = 1
        'maximum value
        ActiveSheet.ChartObjects("Chart 1").Chart.SeriesCollection(1).Values = Range("L14:P14")
        ActiveSheet.ChartObjects("Chart 1").Chart.SeriesCollection(1).XValues = Range("L13: P13")
        ActiveSheet.ChartObjects("Chart 1").Chart.SeriesCollection(1).Format.Fill.ForeColor.RGB = RGB(0, 0, 255) 'RGB for blue
    
    Case Else
        'everything else
        ActiveSheet.ChartObjects("Chart 1").Chart.SeriesCollection(1).XValues = Range("D12: H12")
        ActiveSheet.ChartObjects("Chart 1").Chart.SeriesCollection(1).Values = Range(Range("a12").Offset(Range("o4"), 3), Range("a12").Offset(Range("o4"), 7))
        ActiveSheet.ChartObjects("Chart 1").Chart.SeriesCollection(1).Format.Fill.ForeColor.RGB = RGB(255, 102, 0) 'RGB for orange
    
    End Select
    
    

End Sub

Hold on, now what if i want to pull this data from a different worksheet? I thought I had that down but I guess not....I've attached another example workbook highlighting this problem (its slightly different than the one before). The code is also below.

Sub DynamicCostChartSample()

    Select Case Range("C24")

    Case Is = 1
    
    'This is to pull multiseries line chart from the Financial Assessment Raw Data Page
    ActiveSheet.Shapes("Chart 2").Select
    ActiveChart.SetSourceData Source:=Range("'Financial Assessment Raw Data 1'J172:BD192")
    ActiveSheet.Shapes("Chart 2").Chart.SeriesCollection(1).XValues = Range("'Financial Assessment Raw Data 1'K172:BD172")
    
    'This is to pull individual series from the Financial Assessment Raw Data Page based upon the combo box
    
   Case Else
    ActiveSheet.Shapes("Chart 2").Select
    ActiveChart.SetSourceData Source:=Range(Range("'Financial Assessment Raw Data 1'I171").Offset(Range("'Financial Assessment Raw Data 1'J196"), 2), Range("'Financial Assessment Raw Data 1'I171").Offset(Range("'Financial Assessment Raw Data 1'J196"), 47))
    ActiveSheet.Shapes("Chart 2").Chart.SeriesCollection(1).XValues = Range("'Financial Assessment Raw Data 1'K172:BD172")

    End Select

 
End Sub

I keep getting a "method range of object _global failed" error

Please disregard, problem solved - and that was the wrong code - noob move on my part