vba chart copy from one sheet to another. (wish to use sheet index reference instead of name)

Question

I'm a newbie in VBA. I have created a column chart in sheet 1 and want to move it to sheet 2.

I'm trying to use sheet index as reference but not a specific names.

I have come up with some codes and the part starting with activesheet.chartobjects.select (6th last line) is where problems start ...

Please help and I really appreciate the help and kindness here.

Option Explicit

Sub createClusteredBarChart()
    Dim myworksheet As Worksheet
    Dim mysourcedata As Range
    Dim mychart As Chart
    Dim mychartdestination As Range

    Set myworksheet = ThisWorkbook.Worksheets("sales figures")

    With myworksheet
        Set mysourcedata = .Range("a1:f6")

        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
    End With

    mychart.SetSourceData Source:=mysourcedata    
End Sub

Answer 1

Try this code

Remarks:

1. Read code's comments
2. Check that I have reordered some lines to make the "With objects" more readable

---

Option Explicit
Sub createClusteredBarChart()

    ' Declare objects
    Dim myworksheet As Worksheet
    Dim mysourcedata As Range
    Dim mychart As Chart
    Dim mychartdestination As Range

    ' Set source worksheet
    Set myworksheet = ThisWorkbook.Worksheets("sales figures")

    With myworksheet
        ' Set source data
        Set mysourcedata = .Range("a1:f6")

        ' Set destination range
        Set mychartdestination = .Range("A2:z10")

        ' Create blank chart
        Set mychart = .Shapes.AddChart(XlChartType:=xlColumnClustered, _
        Left:=mychartdestination.Cells(1).Left, Top:=mychartdestination.Cells(1).Top, _
        Width:=mychartdestination.Width, Height:=mychartdestination.Height).Chart
    End With

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

        ' Set chart's source data
        .SetSourceData Source:=mysourcedata

    End With

    ' This line will move the chart to another worksheet (in this example sheet index = 2) (be careful when you have hidden sheets)
    mychart.Location xlLocationAsObject, ThisWorkbook.Worksheets(2).Name

End Sub

Answer 2

The Copy method is not supported for chart objects embedded on a worksheet, only for chart sheets (charts held in their own sheet). You have to copy it as a Shape

So replace

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

With

   myworksheet.shapes(1).copy  'assuming it's the only object on the sheet
   Sheets("Sheet18").Range("a1").PasteSpecial