VBA Code: Create scatter plot with fixed x range from different sheet and y range varies based value of cell in current sheet

[Hello_World_Print]

I want to make a VBA code where both the x and y range are set from a different sheet (sheet2). The x range is dates and will remain in the same column no matter which dataset is imported into (sheet2). I know how to have a fixed y range from (sheet2) but I want the y range to change based on a cell value in (sheet6). For example, if cell(3,2) in sheet6 is Desk, I want the code to search through row(1) in sheet2 to find Desk. Once Desk is found, I want my new y range to be that column. The date range would be something like Worksheets(2).Range("A3:A15000") and the y range would be same length. I want this scatter plot to be printed on sheet6. This will also get repeated for sheet3 and sheet4. Row(1) in sheet2 is the heading for the data in that column.

If cell(3,2) in sheet6 is changed to Chair, I want the scatter plot to change y range once it finds Chair in sheet2 and sets the new range to that column instead. This is replicated to sheets3 and 4 so the scatter plot will have 3 different datasets.

Not sure if my question makes sense but would appreciate the help!

 

[Hello_World_Print]

I have this so far but y range is fixed and I don't want that

Sub MakeChart()
Dim rX1 As Range, rY1 As Range, rX2 As Range, rY2 As Range, rX3 As Range, rY3 As Range
Dim rChartPos As Range
Dim chtO As ChartObject

Set rX1 = Worksheets(2).Range("A3:A15000"): Set rY1 = Worksheets(2).Range("B3:B15000")
Set rX2 = Worksheets(3).Range("A3:A15000"): Set rY2 = Worksheets(3).Range("B3:B15000")
Set rX3 = Worksheets(4).Range("A3:A15000"): Set rY3 = Worksheets(4).Range("B3:B15000")

Set rChartPos = Worksheets("Sheet6").Range("C2:J15") ' location and dimensions of the chart
With rChartPos
    Set chtO = .Parent.ChartObjects.Add(.Left, .Top, .Width, .Height)
    chtO.Name = "MyChart"
End With

With chtO.Chart

    .ChartType = xlXYScatterLines
    
    ' add first series
    With .SeriesCollection.NewSeries
        .XValues = rX1
        .Values = rY1
        .Name = "S1"
    End With
    
    ' add second series
    With .SeriesCollection.NewSeries
        .XValues = rX2
        .Values = rY2
        .Name = "S2"
   End With
    
    ' add third series
    With .SeriesCollection.NewSeries
        .XValues = rX3
        .Values = rY3
        .Name = "S3"
   End With

    .SetElement (msoElementChartTitleAboveChart)
    .ChartTitle.Text = "My Chart Title"

End With
    

End Sub