dynamic charts - problem with copy

[maggym]

I have a small survey. Before knowing the results, I created for the first
question, on the first worksheet, sample responses and a dynamic chart - a
single-series bar chart. For the other questions, I copied that worksheet.
The chart on the first sheet works perfectly. The charts on the other sheets
do not work properly. The series function for these charts reflects the sheet
that the chart is on, but the source data for these charts points back to the
original chart in the first worksheet. So, any changes that I make to the
first sheet are reflected in all the charts. Any changes that I make to the
subsequent sheets do not affect their corresponding charts. Can anyone please
tell me what is the problem and its solution?
Thanks

 

[Jon Peltier]

The series on the first sheet has a formula that refers to dynamic
names, something like

Book1.xls!YRange

Note this name has a workbook scope.

The chart you copied normally would stay linked to the sheet it is
embedded in, because its formulas link to cell addresses, like

Sheet1!$A$1:$A$10

Note this address has a worksheet scope.

The problem here is a bit complicated. You need to set up a workbook
that contains only a master sheet with data and chart. Define the names
as worksheet-scope. In Excel <=2003 you do this by prefixing the name of
the name by the sheet name in the first field of the Define Names dialog:

Sheet1!YRange

or if the name includes spaces or other bad characters:

'Sheet 1'!YRange

In 2007 you can select the scope of the name in the corresponding
dialog. In all versions, there's a much better (and free) Name Manager
available, at http://jkp-ads.com, which makes it simple to define and
redefine names easily.

Now define the chart data in terms of this new name. Save the workbook
with this master sheet. Move the master sheet into your workbook, and
the links remain. Reopen the master workbook, and move the master sheet
again into your workbook, and repeat as many times as necessary.

- Jon

 

[maggym]

Thanks Jon,

Your solution worked, but it has now created a new problems. In my original
workbook, I had included a command button that would generate a copy the
first worksheet for the next question. I want that button in the master so
that it will be included in each copy of that sheet. I don’t know how to
write that code. It should take into account that the master and working
workbook are both open at the same time, or that the working workbook is open
and the button will open and close the master after making a copy. Doing this
in the master file doesn’t seem possible. I am a beginner with VBA, but can
manage.

The second problem is that I had modified the colors for the master, but
your method uses a new workbook with the default palette. The formatting is
thus not what I wanted. How do I get the custom color palette transferred to
the new workbook along with the copied sheet?

While your method worked, I still don’t understand why mine didn’t. As you
said, the defined names that I created had a workbook scope. The Series
function for each chart in the workbook included the defined names, not
absolute references ie Sheet2! Yrange, not Sheet2! $A$1:$A$10. Wouldn’t
that just do the trick and link the copied charts to the sheets that they are
in?

Thanks in advance,
Maggy

 

[Jon Peltier]

You can copy the palette from one workbook to another in Excel 2003:
Tools menu > Options > Color tab, Copy colors from, and select the
workbook with the color palette you want to use.

In 2007, you can select the colors from the Page Layout tab, Themes
group, Colors dropdown, and choose the theme you want. Any custom themes
will appear in the list.

Your approach doesn't work in Excel 2003 or 2007. In Excel 2003, when
you copy a sheet with a chart that uses names (not addresses) to
reference chart data on the worksheet, if the names are workbook-level,
the names are retained. If the names are worksheet-level, the names are
replaced by arrays of values. In Excel 2007, either type of name is
converted to the corresponding address.

- Jon