Cells with Duplicate Name on Other Worksheets

[JAD]

I have three worksheets, Me, Vm & Pl, in which I named cells for use in
macro's and other functions. The next step is to copy each worksheet and
rename them Ve, Vm & Vl. As expected, the cell names of the newly copied
worksheets stay the same with the exception of the worksheet name being
different. I assumed that I cannot have two cells in a workbook, not
worksheet, with the same name. If I can, it would reduce the time to rename
cells in the copied worksheets. Also, when I copy the VB associated with the
original worksheet to the copy worksheet, all I should need to do is find and
replace the worksheet names. Can someone help clarify if I can have duplicate
names or will need to delete the second and third occurance of the names and
enter the corrected versions? Any help would be appreciated. Thank You, JAD

 

[Dave Peterson]

Names can be either global (workbook level) or local (worksheet level).

When you copy a worksheet that has names in it and keep the new sheet in the
same workbook, the new copy has the same names, but they're local to that
worksheet.

(The original worksheet's names can be global or local. But the new worksheet
names are alway local.)

And you can have lots of local names that share the same name (kind of).

If you look at the name of a range that has a local name, you'll see something
like:
'Sheet 99'!TheNameHere

So you could have lots of worksheets that have that same "TheNameHere" used in
the worksheet level name.

If you create a name using insert|Name|define (xl2003 menus), you can specify
that the name be local by including the worksheet name in that dialog.

Names in workbook:
'Sheet 99'!TestName
Refers to
='sheet 99'!$a$1:$b$1

If you don't include the sheet name in the textbox at the top, then the name
will be global (workbook level).

You can fiddle around with deleting the workbook names and creating new
worksheet level names, but that would be a pain.

Instead, get a copy of Jan Karel Pieterse's (with Charles Williams and Matthew
Henson) Name Manager:

You can find it at:
NameManager.Zip from http://www.oaltd.co.uk/mvp

One of the several nice features is the ability to localize or globalize a name.

==========
I'm not sure what the second part of your question means. If you have code
under the worksheet (for controls from the control toolbox toolbar or for
worksheet events), then I wouldn't think that you'd be refering to the worksheet
by name. (I'd use the Me keyword.)

========
And if you want to refer to a named range in code, you could specify the
worksheet and range name:

dim myRng1 as Range
dim myRng2 as range

set myrng1 = worksheets("Me").range("testname")
set myrng2 = worksheets("Ve").range("testname")