Get Excel Chart to Change on Multiple Worksheets

[Sherifa]

I have created two charts in my source worksheet Sheet4, but I want to adjust the range for 42 worksheets.
I'm less than a week old to Bloomberg, so didn't know the best way.
The data positioning is the same for all the worksheets.
I've added secondary axis etc, so want the copy and paste to be able to adjust to each worksheets data.
How can I go about this?

I've tried to add the range, but don't know what you meant or how to fill chart with actual values (rather binding it to sheet's range)

Sub SetSourceData()Dim Wkb As Excel.Workbook
Dim ws As Worksheet


 
 Set Wkb = ThisWorkbook
        ws_count = Wkb.Worksheets.Count
                
                     
        'Begin loop
        For i = 3 To ws_count
        
        Set TargetSheet = Wkb.Worksheets(i)
        
      Charts("AllDeals").SetSourceData Source:=Wkb.Worksheets(i).Range("q3:t14"), _
        PlotBy:=xlColumns
 
 
 Next
 
End Sub