When I copy let's say Sheet1 to Sheet2 using Click-Tab, Ctrl+Drag-and-drop,
all local range names in Sheet1 are also copied and are automatically defined
as local to Sheet2. Thereafter, local range names are independently
maintained in each worksheet.

The scenario is when after the fact I need to define a new range name let's
say NewRangeName for Cell $A$1 in Sheet1, how can I define the same
NewRangeName as local to Sheet2 without having to repeat the copy process
Sheet1 to Sheet2. I tried qualifying NewRangeName by using the sheet name
(=Sheet1!$A$1) but as soon as NewRangeName is defined in Sheet2 as
(=Sheet2!$A$1), the latter takes precedence and becomes unique to Sheet2.

Any advice is greatly appreciated.

Make sure you include the sheetname in the name of the name <vbg.

In the insert|name|define dialog (xl2003 menus), you'd use:

Names in workbook:
Sheet2!NewRangeName
or
'Sheet 222'!NewRangeName
(if it needed to be surrounded by apostrophes.

=====
If you have to localize (or globalize) and existing name, you'll want to use Jan
Karel Pieterse's (with Charles Williams and Matthew Henson) Name Manager:
NameManager.Zip from http://www.oaltd.co.uk/mvp

In fact, if you're working with names, you'll want this!

Ian wrote:

When I copy let's say Sheet1 to Sheet2 using Click-Tab, Ctrl+Drag-and-drop,
all local range names in Sheet1 are also copied and are automatically defined
as local to Sheet2. Thereafter, local range names are independently
maintained in each worksheet.

The scenario is when after the fact I need to define a new range name let's
say NewRangeName for Cell $A$1 in Sheet1, how can I define the same
NewRangeName as local to Sheet2 without having to repeat the copy process
Sheet1 to Sheet2. I tried qualifying NewRangeName by using the sheet name
(=Sheet1!$A$1) but as soon as NewRangeName is defined in Sheet2 as
(=Sheet2!$A$1), the latter takes precedence and becomes unique to Sheet2.

Any advice is greatly appreciated.

--

Dave Peterson