Automatically change chart data source

[Mon_Star]

Hi,

Some guidance would be appreciated. I have a performance report for each team in my workplace. I have dynamically based the charts on the one cell where I have my validation list of team names. I subsequently have a macro set up to run through the list and save the report to PDF.

I'm getting stuck at one chart where I graph as a bar chart the performance of each project the team has worked on. Each team can work on 40 projects or 20 projects, all different. I want to be able to dynamically change the entire data source of the chart based off a single cell.

I keep Googling for answers, but I just get offset answers that only reference how to automatically update a chart after adding or deleting data. I want to completely change the data source.

Help! I feel stuck

Thanks,
Mon_Star

 

[rizwan_planning]

Can u link bar chart data to your validation list

 

[thisoldman]

Here's a quick example that I ran.

Three teams named A, B, and C.
The three chart source data ranges are named DataA, DataB, and DataC.
The data validation dropdown cell is named TeamSelection, and its possible values are "A", "B", or "C".

Sub ChangeActiveChartDataSource()
''' Allows user to select chart source data through a data validation dropdown.
'''
    Dim rng As Range
    
    Select Case Range("TeamSelection").Value
        Case "A"
            Set rng = Range("DataA")
        Case "B"
            Set rng = Range("DataB")
        Case "C"
            Set rng = Range("DataC")
    End Select
    
    ActiveChart.SetSourceData Source:=rng
End Sub