Adjust Chart for all WorkSheets VBA

[Sherifa]

I want to be able to copy and paste an Excel Chart using vba to set number of worksheets, but to adjust the range for the charts to that particular spreadsheet, this how far I've gotten but I have an error

Sub CopyCharts()
Dim Wkb As Excel.Workbook
Dim ws As Worksheet
Dim ws_count As Integer
Dim i As Integer




Sheet4.ChartObjects("ALLDeals").Copy


        Set Wkb = ThisWorkbook
        ws_count = Wkb.Worksheets.Count
                
                     
        'Begin loop
        For i = 4 To ws_count
        
        Set TargetSheet = Wkb.Worksheets(i)
                        
        TargetSheet.ChartObjects("ALLDeals").Paste
        
        
Sheet4.ChartObjects("ALLVolume").Copy
                           
         TargetSheet.TargetSheet.ChartObjects("ALLVolume").Paste


      Next
End Sub

 

[Sherifa]

Works to copy and paste the chart, but it does not adjust the chart to the relevant spreadsheet data.
It does not position the charts in the range selected when it is pasted.

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
                
                     
        'Begin loop
        For i = 4 To ws_count
        
[B]        Set TargetSheet = Wkb.Worksheets(i)[/B]
[B]        Sheet4.ChartObjects("ALLDeals").Copy[/B]
[B]        Range("q19").Select[/B]
[B]        TargetSheet.Paste[/B]


[B]        Sheet4.ChartObjects("ALLVolume").Copy[/B]
[B]        Range("aa19").Select[/B]
[B]        TargetSheet.Paste[/B]


      Next
      
End Sub

 

[Sektor]

Could you explain more in detail what you want to do?

 

[Sherifa]

Each Excel sheet has table data in the same place, which the charts are based on.
I want the charts to change as they are pasted into a new worksheet

 

[Sektor]

Why would you need paste charts? Wouldn't it better to create a chart one time and adjust it to range?

 

[Sherifa]

I have created two charts, 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 range is the same for all the worksheets.
How can I go about this?

 

[Sektor]

If the range is the same for all worksheets, then there's a way to fill chart with actual values (rather binding it to sheet's range).
Could you post the sample workbook to some cloud storage (with sensitive data removed)?

 

[Sherifa]

Is there any way you can guide me through, all data is sensitive, because this is a work document.
Or refer me to a source that explains?
I've searched online, but can't quite find what I'm looking for.

 

[Sherifa]

fill chart with actual values (rather binding it to sheet's range)
Do I need vba for this?

 

[Sektor]

Yes, it requires VBA. Here is the sample code which fills chart with values.