VBA图表副本从一张纸到另一张.(希望使用表索引参考而不是名称)

Question

我是VBA的新手。我在表1中创建了一个列图，并想将其移至表2。

我试图将表索引用作参考，而不是特定的名称。

我提出了一些代码，从activesheet.chartobjects.select开始的零件(最后一行(是问题开始的地方...

请提供帮助，我非常感谢这里的帮助和友善。

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

尝试此代码

备注：

1. 阅读代码的评论
2. 检查我是否已经重新排序一些行以使"使用对象"更可读

---

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

仅支持嵌入工作表中的图表对象的复制方法，仅用于图表(在自己的工作表中保存的图表(。您必须将其复制为形状

所以更换

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

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