copy a graph from workbook A to workbook B

[Octonet]

I have 2 workbooks, A.xlsm and B.xlsm
Workbook A contains measurement data from an object.
Workbook B is identical to workbook A, but with measurement data from another object.
In workbook A I made a lot of graphs from these data.
Is it possible to copy the graphs from workbook A to workbook B so the graphs take the data from workbook B.
I tried all ways of copy / paste special, but I always had to change the workbook where the graph had to get its data.
A and B are two identical workbooks for 2 different objects.
If I have to do this manually, it is a lot of work. Workbook A contains 50 graphs and each graph containe 20 bars.

 

[MoshiM]

Try something like this.

Sub Copy_Charts()

Dim CHO As ChartObject, _
WB_A As Workbook, WB_B As Workbook, Y As Long, Z As Long

Dim Source_WS As Worksheet, New_Worksheet As Worksheet, WB_A_STR As String, New_Top As Double

Set WB_A = Workbooks("A.xlsm")
Set WB_B = Workbooks("B.xlsm")

WB_A_STR = "[" & WB_A.Name & "]"

Set New_Worksheet = WB_B.Worksheets.Add

Set Source_WS = WB_A.Worksheets(" SHEET NAME GOES HERE ")

For Each CHO In Source_WS.ChartObjects

    CHO.Copy
    New_Worksheet.Paste
    
Next CHO


New_Top = 0

For Z = 1 To New_Worksheet.ChartObjects.Count

    With New_Worksheet.ChartObjects(Z)
    
        If Z Mod 4 = 1 Then 'Create a grid of charts
        
            .Left = 0
            .Top = New_Top
            
        Else
            
            .Top = New_Top
            .Left = New_Worksheet.ChartObjects(Z - 1).Left + New_Worksheet.ChartObjects(Z - 1).Width

            If Z Mod 4 = 0 Then New_Top = New_Worksheet.ChartObjects(Z - 3).Top + New_Worksheet.ChartObjects(Z - 3).Height
            
        End If

        With .Chart.FullSeriesCollection 'Replace formula references to Workbook A
            For Y = 1 To .Count
            
                With .ITEM(Y)
                    .Formula = Replace(.Formula, WB_A_STR, vbNullString)
                End With
                
            Next Y
        End With
        
    End With
    
Next Z

End Sub

 

[Octonet]

Thanks, I' ll try this and let You know the result.

 

[Octonet]

It worked for me.
Thanks MoshiM

Can be closed