Worksheet copy problem - local names
 

Hi all

Name
'Sheet (1)'!MyName refers to
=Sheet1!$A$1:$A$100

'Sheet (2)'!$B$2 contains a formula
=SUM('Sheet (1)'!MyName)

If I dulicate 'Sheet (2)' (Edit/Copy or move sheet)
then 'Sheet (3)'!$B$1 contains the formula
=#N/A
when I would prefer it to contain the formula
=SUM('Sheet (1)'!MyName)

Is there a general way to achieve this (other than VBA?)

Please do NOT suggest that I enter =SUM('Sheet (1)'!MyName)
in some cell in Sheet1 and then refer to that cell in 'Sheet (2)'!$B$1
rather than the name
I KNOW that that would successfully solve the precise example stated, but in
reality I have a vast number of formulae displaying this problem in the
sheet to be copied.

--
Return email address is not as DEEP as it appears

Slight misprint. Any reference to cell $B$2 anywhere should read $B$1.

"Jack Sheet" wrote in message
...
Hi all

Name
'Sheet (1)'!MyName refers to
=Sheet1!$A$1:$A$100

'Sheet (2)'!$B$2 contains a formula
=SUM('Sheet (1)'!MyName)

If I dulicate 'Sheet (2)' (Edit/Copy or move sheet)
then 'Sheet (3)'!$B$1 contains the formula
=#N/A
when I would prefer it to contain the formula
=SUM('Sheet (1)'!MyName)

Is there a general way to achieve this (other than VBA?)

Please do NOT suggest that I enter =SUM('Sheet (1)'!MyName)
in some cell in Sheet1 and then refer to that cell in 'Sheet (2)'!$B$1
rather than the name
I KNOW that that would successfully solve the precise example stated, but
in
reality I have a vast number of formulae displaying this problem in the
sheet to be copied.

--
Return email address is not as DEEP as it appears

I get quite close to the desired solution by inserting a new worksheet
then block the whole of the source worksheet, copy it to the clipboard
and then paste it into cell A1 of the newly created worksheet.

The problem with that is that it does not create local names that should be
local to the newly created sheet and which would have been created using the
Edit/Copy Sheet method
(and the print page setups are not copied either, of course, nor autofilter
settings etc)


"Jack Sheet" wrote in message
...
Slight misprint. Any reference to cell $B$2 anywhere should read $B$1.

"Jack Sheet" wrote in message
...
Hi all

Name
'Sheet (1)'!MyName refers to
=Sheet1!$A$1:$A$100

'Sheet (2)'!$B$2 contains a formula
=SUM('Sheet (1)'!MyName)

If I dulicate 'Sheet (2)' (Edit/Copy or move sheet)
then 'Sheet (3)'!$B$1 contains the formula
=#N/A
when I would prefer it to contain the formula
=SUM('Sheet (1)'!MyName)

Is there a general way to achieve this (other than VBA?)

Please do NOT suggest that I enter =SUM('Sheet (1)'!MyName)
in some cell in Sheet1 and then refer to that cell in 'Sheet (2)'!$B$1
rather than the name
I KNOW that that would successfully solve the precise example stated,
but
in
reality I have a vast number of formulae displaying this problem in the
sheet to be copied.

--
Return email address is not as DEEP as it appears