通过宏将图表系列源设置为命名范围 [英] Set a Chart series source to a named range via Macro

问题描述

我有一个带有现有图表的工作表，并使用适当的动态命名范围来提供该图表正确的数据。

I have a sheet with a existing chart and the proper dynamic named ranges to feed that chart the right data.

我的问题是工作表是一个要复制的模板，副本的图表不再指向指定的范围。命名的范围将被复制到工作表（范围是工作表特定的），因此似乎应该只是更新系列源数据而已。好的旧记录为我记录了手动执行操作时提供了以下语法：

My problem comes that the sheet is a template that is copied and the copies' charts don't point to the named ranges anymore. The named ranges get copied to the sheet (the ranges are sheet specific) so it seems like it should be a matter of just updating the series source data. Good old record gave me the following syntax when I recorded doing that manually:

ActiveChart.SeriesCollection(1).XValues = "=='Risk 1'!PDates"
ActiveChart.SeriesCollection(1).Values = "=='Risk 1'!Plan"
ActiveChart.SeriesCollection(2).XValues = "=='Risk 1'!ADates"
ActiveChart.SeriesCollection(2).Values = "=='Risk 1'!Actuals"

我尝试使用修改后的版本将它们重置为指定的范围：

I've attempted to reset them to the named ranges with a modified version of that:

For Each Cht In TempSheet.ChartObjects
    'Series 1 is the Plan
    Cht.Activate 
    ActiveChart.SeriesCollection(1).XValues = "=='" & TempSheet.Name & "'!PDates"
    ActiveChart.SeriesCollection(1).Values = "=='" & TempSheet.Name & "'!Plan"

    'Series 2 is the Actuals
    ActiveChart.SeriesCollection(2).XValues = "=='" & TempSheet.Name & "'!ADates"
    ActiveChart.SeriesCollection(2).Values = "=='" & TempSheet.Name & "'!Actuals"
Next

我遇到了一个错误，应用程序定义或对象定义错误，在试图更新第一个系列的X值的语句中。

I hit an error, "Application-defined or object-defined error", at the statement that tries to update the X Values for the first series.

我还尝试在其中添加PlotArea select语句，以防万一那就是问题所在，但是并不能解决问题。我检查了应该对集合进行正确评估的语句（例如 =='& TempSheet.Name&'！PDates 进行评估）到 =='风险1'！PDates，这就是该系列所要指出的。）

I've also tried adding the PlotArea select statement in, just in case that was the issue, but that didn't fix the problem. I checked that the statements the collections are supposed to be getting set to evaluate correctly (ex. "=='" & TempSheet.Name & "'!PDates" evaluates to "=='Risk 1'!PDates", which is what the series should be pointed at).

有了这一点，我感到很困惑，不胜感激任何人都可以提供的任何帮助。

With that, I'm fairly well stumped and would appreciate any help anyone could provide. Thanks in advance!

推荐答案

如果模板工作表具有所需的数据和图表，但具有伪数据，将其另存为正式的Excel模板。然后，当您需要这些工作表之一时，右键单击工作表选项卡，然后单击插入...。在对话框中选择模板，然后单击确定。当基于模板插入此工作表时，其图表将链接到工作表中的名称。

If the "template" worksheet has the data and chart the way you want, but with dummy data, save it as an official Excel template. Then when you need one of these sheets, right click on a sheet tab and click Insert... Select the template in the dialog and click OK. When this sheet is inserted based on the template, its chart will be linked to the Names in the sheet.

这篇关于通过宏将图表系列源设置为命名范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！