EXCEL VBA Charts, Dynamic Chart Range

[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.
I want the charts to have the same formatting, and values etc, I don't want it to create a new charte
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

Alternative code with error

[/B]Sub CopyCharts()



Dim Wkb As Excel.Workbook
Dim ws As Worksheet
Dim ws_count As Integer
Dim i As Integer




        Set Wkb = ThisWorkbook
        ws_count = Wkb.Worksheets.Count
        
Dim chDeals As Chart
Dim chVolume As Chart
Set chDeals = Worksheets("ALL") = Charts("ALLDeals")
Set chVolume = Worksheets("ALL").ChartObjects("ALL").Charts("ALLVolume")


                
                     
        'Begin loop
        For i = 4 To ws_count
        
        Set TargetSheet = Wkb.Worksheets(i)
        
        chDeals.Copy
        Range("q19").Select
        TargetSheet.Paste
            With ChartTitle = ws.Name
            End With
        
        Sheet4.ChartObjects("ALLVolume").Copy
        chVolume.Copy
        Range("aa19").Select
        TargetSheet.Paste
           With ChartTitle = ws.Name
            End With
      Next
      
End Sub


[B]