vba 使用 Excel 中图表的目标工作表数据将图表从一张工作表复制到另一张工作表

Copy a chart from one sheet to another using the target sheet data for the chart in Excel

提问by Ari

I have several worksheets which have different data but organized in the same way (same number of col and rows). I created several charts in sheet1 which I would like to copy to sheet2. while the normal copy/paste copies the charts to sheet2, the charts is still referring to data in sheet1, not in sheet2. How can I automatically make them use sheet2 data rather than sheet after copying?

我有几个工作表，它们具有不同的数据，但以相同的方式组织（相同数量的列和行）。我在 sheet1 中创建了几个图表，我想将其复制到 sheet2。虽然正常的复制/粘贴将图表复制到工作表 2，但图表仍然引用工作表 1 中的数据，而不是工作表 2 中的数据。复制后如何自动让他们使用 sheet2 数据而不是 sheet？

As a work around, I tried copying sheet1 and called it sheet2 (which copies all data and charts), then copy and pasted the real sheet2 data in this new sheet. This works, but I was hoping there is a faster way and perhaps a macro that copies all charts from sheet1 to sheet2 and automatically updates the references.

作为一种解决方法，我尝试复制 sheet1 并将其称为 sheet2（它复制所有数据和图表），然后将真实的 sheet2 数据复制并粘贴到这个新工作表中。这有效，但我希望有一种更快的方法，也许是一个宏，可以将所有图表从 sheet1 复制到 sheet2 并自动更新引用。

采纳答案by Ari

So something like this worked for me. CopyCharts copies all charts from a source sheet to a target sheet. Then SetChartRef sets the reference of the charts in the target to what I want them to be. In this example I know which chart number is what. I guess it can be improved so that it uses the chart names instead.

所以这样的事情对我有用。CopyCharts 将所有图表从源工作表复制到目标工作表。然后 SetChartRef 将目标中图表的引用设置为我想要的。在这个例子中，我知道哪个图表编号是什么。我想它可以改进，以便它使用图表名称。

Also, for some reason I get run time errors if I don't have delays between copying and pasting, hence the wait functions.

此外，由于某种原因，如果我在复制和粘贴之间没有延迟，则会出现运行时错误，因此等待功能。

    Sub DeleteEmbeddedCharts(target As String)

    Dim wsItem As Worksheet
    Dim chtObj As ChartObject
        For Each chtObj In ThisWorkbook.Worksheets(target).ChartObjects
            chtObj.Delete
        Next
End Sub

Sub SetChartRef(target As String)

    Dim cht As ChartObject
    Dim i As Integer

    'i specifies which chart to set its data references
    i = 0
    For Each cht In ThisWorkbook.Worksheets(target).ChartObjects
        If i = 0 Then
            cht.Chart.SeriesCollection(1).Values = "=" & target & "!$I:$I"
            cht.Chart.SeriesCollection(2).Values = "=" & target & "!$J:$J"
        ElseIf i = 1 Then
             cht.Chart.SeriesCollection(1).Values = "=" & target & "!$I:$I"
             cht.Chart.SeriesCollection(2).Values = "=" & target & "!$J:$J"
        ElseIf i = 2 Then
            cht.Chart.SeriesCollection(1).Values = "=" & target & "!$I:$I"
            cht.Chart.SeriesCollection(2).Values = "=" & target & "!$J:$J"
        ElseIf i = 3 Then
            cht.Chart.SeriesCollection(1).Values = "=(" & target & "!$H," & target & "!$H," & target & "!$H," & target & "!$H)"
            cht.Chart.SeriesCollection(1).XValues = "=(" & target & "!$E," & target & "!$E," & target & "!$E," & target & "!$E)"
         ElseIf i = 4 Then
            cht.Chart.SeriesCollection(1).Values = "=(" & target & "!$H," & target & "!$H," & target & "!$H," & target & "!$H)"
            cht.Chart.SeriesCollection(1).XValues = "=(" & target & "!$E," & target & "!$E," & target & "!$E," & target & "!$E)"
        ElseIf i = 5 Then
            cht.Chart.SeriesCollection(1).Values = "=" & target & "!$I:$I"
            cht.Chart.SeriesCollection(2).Values = "=" & target & "!$J:$J"
        End If
        i = i + 1
    Next


End Sub

Sub CopyCharts(source As String, target As String)

    Dim chtObj As ChartObject
    'First delete all charts from target sheet
    DeleteEmbeddedCharts (target)

    'Some delay
    Application.Wait Now + TimeSerial(0, 0, 1)

    For Each chtObj In ThisWorkbook.Worksheets(source).ChartObjects
        With ThisWorkbook.Worksheets(target)
            .Activate
            chtObj.Copy
            'Paste in row T1+i
            Range("T1").Offset(i).Select
            .Activate
            Application.Wait Now + TimeSerial(0, 0, 1)
            .Paste
            Application.Wait Now + TimeSerial(0, 0, 1)
            i = i + 10
            .Activate
        End With
    Next chtObj

    'Set the data references to target sheet
    SetChartRef (target)

End Sub

回答by Jon Peltier

The easiest way to copy a chart to another sheet and have the chart link to data on the new sheet, isn't to copy the chart. The easiest way is to copy the sheet including the chart, then change the data on the copied sheet.

将图表复制到另一个工作表并将图表链接到新工作表上的数据的最简单方法不是复制图表。最简单的方法是复制包含图表的工作表，然后更改复制的工作表上的数据。

The second-easiest way, if the chart's data is organized simply, is to use Select Data from the ribbon or the right-click menu, and change the range indicated in the Chart Data Range RefEdit at the top of the dialog.

如果图表的数据组织简单，第二种最简单的方法是使用功能区中的“选择数据”或右键单击菜单，然后更改对话框顶部的“图表数据范围参考编辑”中指示的范围。

A tedious way is to change all of the sheet references in all of the chart's series formulas, for example, changing all instances of Sheet1 in the following formula to Sheet2: =SERIES(Sheet1!$B$1,Sheet1!$A$2:$A$4,Sheet1!$B$2:$B$4,1)

一种繁琐的方法是更改​​图表的所有系列公式中的所有工作表引用，例如，将以下公式中 Sheet1 的所有实例更改为 Sheet2： =SERIES(Sheet1!$B$1,Sheet1!$A$2:$ A$4,Sheet1!$B$2:$B$4,1)

As @sancho.s points out, you can also do this with VBA using code posted in my Change Series Formulatutorial. These algorithms are built into my commercial Excel add-in software.

正如@sancho.s 指出的那样，您也可以使用我的更改系列公式教程中发布的代码使用 VBA 执行此操作。这些算法内置于我的商业 Excel 插件软件中。

回答by sancho.s ReinstateMonicaCellio

I've used Jon Peltier's Change Series Formulaa lot (it is actually linked in the middle of the page provided in the answer by GGuess). It is an add-in that can be obtained here.

我经常使用 Jon Peltier 的Change Series Formula（它实际上链接在 GGuess 的答案中提供的页面中间）。这是一个可以在此处获得的插件。

It is extremely useful, and it probably covers the needs of most cases of this type. It provides a convenient interface (UserForm) to search and replace strings in chart series formulas. It is actually more versatile than a "change source worksheets", since one can use search and replace for changing other parts of series formulas for many series at once.

它非常有用，它可能涵盖了大多数此类情况的需求。它提供了一个方便的界面 (UserForm) 来搜索和替换图表系列公式中的字符串。它实际上比“更改源工作表”更通用，因为可以使用搜索和替换一次更改多个系列的系列公式的其他部分。

回答by GGuess

Pelter has a good discussion on how to use a macro to edit the plot equations to reference the data in the current worksheet. See the article at http://peltiertech.com/WordPress/make-a-copied-chart-link-to-new-data/

Pelter 对如何使用宏编辑绘图方程以引用当前工作表中的数据进行了很好的讨论。请参阅http://peltiertech.com/WordPress/make-a-copied-chart-link-to-new-data/ 上的文章