Range in chart changes when I create a new WS

[magnus.blomquist]

Hi all,

The workbook is set up so that gearbox oil samples are collated in a table in the "blank" WS with bar charts that reference cells in the blank WS. A macro then duplicates the blank WS charts n'all, renames it (gives it the name of the gearbox in cell A2, and then goes back to the "blank" WS, and I can then select another gearbox to create another individual WS.

The problem is (it used to work!!) that the charts on the new WS's created have started referencing back to the cells in the "blank" WS, and not the cells in the re-named WS.

for example, Series 1 is:
=SERIES(blank!$E$5:$F$5,(blank!$G$4,blank!$I$4,blank!$AD$4),(blank!$G$5,blank!$I$5,blank!$AD$5),1).

it should be:
=SERIES(IGB01!$E$5:$F$5,(IGB01!$G$4,IGB01!$I$4,IGB01!$AD$4),(IGB01!$G$5,IGB01!$I$5,IGB01!$AD$5),1) where IGB01 is the name of a gearbox (and name of WS). This reference should change when a new WS is created.

It does work with MS Excel 2003, but not 2007 which we're working with.

I've thought of using INDIRECT, but can't seem to get the code right?

Any ideas or need more info??

[magnus.blomquist]

Works in MS Excel 2010...