Set mychartdestination = .Range("A2:z10")

Set mychart = .Shapes.AddChart(XlChartType:=xlColumnClustered, _
Left:=mychartdestination.Cells(1).Left, Top:=mychartdestination.Cells(1).Top, _
Width:=mychartdestination.Width, Height:=mychartdestination.Height).Chart

    With mychart
        .Axes(xlValue).MaximumScale = 16000000
        .Axes(xlValue).MajorUnit = 4000000
        .ChartArea.Select
        .ChartArea.Height = 216
        .ChartArea.Width = 360
        .ChartGroups(1).GapWidth = 65

    End With

ActiveSheet.ChartObjects.Select
ActiveSheet.ChartObjects.Copy
Sheets("Sheet18").Range("a1").Paste

mychart.SetSourceData Source:=mysourcedata