Excel Chart range based on the value in a cell in another sheet

Question

I have a sheet (Dashboard) that has multiple Pareto charts, another sheet (Data) brings in the range for each chart via a formula in standard $A$1:$B$2 format.

how do I use these ranges from the Sheet "Data" in the Pareto charts in the "Dashboard"? Chart name is in Data B4 Chart Range is in Data C4 I have code for each chart for troubleshooting below is one from a single chart

Sub FirstChart()
    Dim FirstChartName As String
    Dim FirstChartRange As Range
    
        FirstChartName = Sheets("Data").Range("B4")
        Set FirstChartRange = Worksheets("Data").Range(Sheets("Data").Range("C4").Value)
        Sheets("Dashboard").ChartObjects("FirstChart").Activate
        ActiveChart.ChartArea.Select
        ActiveChart.HasTitle = True
        ActiveChart.ChartTitle.Text = FirstChartName 
        ActiveChart.SetSourceData Source:=FirstChartRange
End Sub

Thanks in advance.

UPDATE: Thanks to @coross24 and @WIL. i have uploaded the file based on their answers to https://gofile.io/d/8HfjQv

Answer 1

Relik,
I've had to post another answer as my reputation isn't high enough to reply with a comment. There's an absolutely filthy work around.... it seems the data does actually populate the graph is you just bypass the error message, and then set the y-axis scale to auto. See below for the code:

Option Explicit

Sub FirstChart()
    Dim FirstChartName As String
    Dim FirstChartRange As String
    Dim rng As Range
    Dim r As Range
    Dim shtData As Excel.Worksheet
    Dim shtDashboard As Excel.Worksheet
    Dim chart As Excel.chart
    Dim tmp As Variant

    Set shtData = ThisWorkbook.Sheets("Data")
    Set shtDashboard = ThisWorkbook.Sheets("Dashboard")
    
    ' get chart name
    FirstChartName = shtData.Range("B4").Value2
    ' get chart range
    FirstChartRange = shtData.Range("C4").Value2
    
    ' change data for first chart
    Set chart = shtDashboard.ChartObjects("FirstChart").chart
    With chart
        .HasTitle = True
        .ChartTitle.Text = FirstChartName
        On Error Resume Next
        .SetSourceData shtData.Range(FirstChartRange)
        On Error GoTo 0
        .Axes(xlValue).MaximumScaleIsAuto = True
    End With
       
End Sub

Hope this helps with your issue!