Worksheet copy problem - local names

[Jack Sheet]

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.

 

[Jack Sheet]

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

 

[Jack Sheet]

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)

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

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