同时复制多个工作表以保留图表引用

I've created a two-worksheet template in Excel - the first sheet is for pretty charts, and the other sheet is for the data that drives those charts. I've written a vb.net 2005 app that can dump in all my data on the second worksheet, and the chart worksheet updates perfectly.

我在Excel中创建了一个双工作表模板 - 第一个工作表用于漂亮的图表,另一个工作表用于驱动这些图表的数据。我写了一个vb.net 2005应用程序,它可以转储第二个工作表上的所有数据,并且图表工作表更新完美。

I would like to do this report several times over in the same workbook. (So the tabs would read 'Person1 - Chart', 'Person1 - Data', 'Person2 - Chart', 'Person2 - Data', etc.)

我想在同一个工作簿中多次做这个报告。 (因此选项卡将显示为“Person1 - Chart”,“Person1 - Data”,“Person2 - Chart”,“Person2 - Data”等)

My solution was to, for every person this report was going to be run for, copy the chart template, and then copy the data template. The problem is that every chart template that is created points to the original data worksheet. How can I set what worksheet each chart worksheet is pointing at?

我的解决方案是,为每个人运行此报告,复制图表模板,然后复制数据模板。问题是创建的每个图表模板都指向原始数据工作表。如何设置每个图表工作表指向的工作表?

Is there a way to copy worksheets in pairs, maintaining the relationship to themselves, rather than to the parent?

有没有办法成对地复制工作表,维持与自己的关系,而不是父母的关系?

3 个解决方案

#1

Yes, select both sheets before you use the copy function. If you're doing it manually, don't forget to deselect afterwards, or you'll be entering data into both.

是,在使用复印功能之前选择两张纸。如果您是手动操作,请不要忘记之后取消选择,或者您将两者都输入数据。

In VBA:

Sheets(Array("Chart template", "Data template")).Select
ActiveWindow.SelectedSheets.Copy After:=Sheets("Data template")

In Interop:

App.ActiveWorkbook.Sheets(New String()
        {"Chart template", "Data template"}).Select()
App.ActiveWindow.SelectedSheets.Copy(After:=
        App.ActiveWorkbook.Sheets("Data template"))

#2

I haven't used a programmed app to make this work, but if you bring the data to the same sheet and set up the sheet using the Page Break Preview to avoid printing the data sheet, then you can copy and paste the sheet over and over and your chart references will stay valid.

我没有使用编程的应用程序来完成这项工作,但是如果您将数据带到同一张纸上并使用分页预览设置工作表以避免打印数据表,那么您可以将工作表复制并粘贴到结束并且您的图表参考将保持有效。

Depending on the amount of data you're using this may not be an ideal solution, but its how I've managed this problem.

根据您使用的数据量,这可能不是一个理想的解决方案,但它是我如何解决这个问题。

#3

You can fix up the references in the Chart after you copy.

复制后,您可以修复图表中的引用。

Use SetSourceData method

使用SetSourceData方法

ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A3:D7")

#1