VBA copied chart keeps on pointing to original data, VBA relinking charts to nesource data

[dastr]

Problem wt VBA - copied chart keeps on pointing to original data, VBA relinking charts to new source data

Dear all,

I have a chart linked to a source data on sheet "Pattern". I want to copy and paste the chart and the table on multiple sheets. I have managed so far to create the multiple sheets in VBA but when I copy and paste the table and charts on the newly created worksheets, I either have all the newly pasted charts linking to the original data
(table) on worksheet "Pattern" or I paste the tables but not the charts.

Can someone help? Ideally I would like to paste a chart and a table on a new worksheet (say WS "English") and have the chart on WS English being linked to the table on the same worksheet?

Here I am attaching the example.
Thanks in advance.






</PRE>

Sub CreateManyWorksheetsAndCopyPattern()
    'Create the worksheets for all regions
    For Each cell In Selection
        ThisWS = cell.Value
        Worksheets.Add after:=Worksheets(Worksheets.Count)
        ActiveSheet.Name = ThisWS
    Next cell
    

' COPY THE PATTERN
    
Worksheets("Pattern").Range("A3:Z10").Copy Destination:=Worksheets("Maths").Range("A2:Z10")
Worksheets("Pattern").Range("A3:Z10").Copy Destination:=Worksheets("English").Range("A2:Z10")
    
End Sub


</PRE>

 

[sijpie]

Sub switchS(Optional sWSName As String)
    'Note this macro needs to be run from the original sheet
    'or needs to be called with the name of the original sheet
    'example: switchS MySheet1
    
    Dim coChrt As ChartObject
    Dim sCrtRng As String, sForm As String
    Dim wsWS As Worksheet, i As Integer
    
    If sWSName = vbNullString Then
        sWSName = ActiveSheet.Name
    End If
    
    For Each wsWS In Worksheets
 '       wsWS.Select
        For Each coChrt In wsWS.ChartObjects
            With coChrt.Chart
                For i = 1 To .SeriesCollection.Count
                    sForm = .SeriesCollection(i).Formula
                    sCrtRng = Replace(sForm, sWSName, wsWS.Name)
                    .SeriesCollection(i).Formula = sCrtRng
                Next
            End With
        Next coChrt
    Next wsWS
End Sub

 

[dastr]

thank you very much! Dank je wel, sijpje