Excel 2007 : Copy chart, change data source

**Mimil** · 02-02-2011, 09:56 AM

Hello,
I have some values, and a chart next to them.
I want to copy this "box" 100 times, but i want every chart to be linked
to the values next to it. The problem is that excel will link all this charts with
the values of the first "box".

I attached an example.

Thank you.

**Mimil** · 02-03-2011, 06:42 AM

seriously...
noone?

**Andy Pope** · 02-03-2011, 07:33 AM

try this,

Sub x()

    Dim rngBoxA As Range
    Dim rngBoxB As Range
    Dim rngData As Range
    Dim lngIndex As Long
    
    Set rngBoxA = Range("B3:J11")
    Set rngData = Range("C6:E7")
    
    Set rngBoxB = rngBoxA.Offset(12)
    Set rngData = rngData.Offset(12)
    
    For lngIndex = 1 To 3
        rngBoxA.Copy rngBoxB
        With ActiveSheet.ChartObjects(ActiveSheet.ChartObjects.Count).Chart
            With .SeriesCollection(1)
                .Values = rngData.Rows(2)
                .XValues = rngData.Rows(1)
            End With
        End With
        Set rngBoxB = rngBoxB.Offset(12)
        Set rngData = rngData.Offset(12)
    Next
    
End Sub

**Mimil** · 02-03-2011, 07:48 AM

Thank you for your answer but it doesn't help me.
Maybe i do something wrong. Can you make my example file work and upload it?
And something else, i don't know much about coding, but it seems that your code has to do with
those 2 particular "boxes", i want to copy this 100 times, so i need a code that refers to any copy of this box i may do.
Thank you.