same Named cells in different sheet
 

Hi all,
In a macro I use a named range. I want to use the same macro in similar
sheets in the same workbook. But each time I name the same range in
another sheet, the first one is canceled. How can I create the name in
all the sheet ?

It is probably possible as when you copy a sheet in the same workbook,
you have the same name in all the sheets.

Thanks
:rolleyes: :rolleyes: :confused:


--
Pierre
------------------------------------------------------------------------
Pierre's Profile: http://www.excelforum.com/member.php...fo&userid=3754
View this thread: http://www.excelforum.com/showthread...hreadid=396828

A named range is a reference that you can call up from anywhere in the
workbook, so if you name it XYZ in Sheet1 and again in Sheet2, the
Sheet1 reference point is lost.

Since you're doing this in a macro, you can make the reference name
unique by concatenating the tab name into the named range. In the
example above, you can call your range XYZSheet1 on sheet1, XYZSheet2
on sheet2, etc. However, your tab names will need to follow naming
rules for ranges: no blanks, no special characters, etc.

If tab names won't do it for you, you could concatenate other data
found on the sheet (as long as it is unique and follows naming rules),
a month name, a timestamp, whatever makes sense for your application.
You might use the INPUTBOX function of VBA to customize an entry.

Allow me to suggest: don't allow a named range to be confused with a
cell reference. For instance if your named range is "GM", don't use
"GM1" as a named range because Excel cannot distinguish between this
named range and cell GM1. It can be done but creates havoc later on.

Thanks a lot Dave.

You are right I can use the worksheet.name & RangeName.
Actually, that's what I did but as when copying the sheet in the same
workbook you generate the same named range in both sheet I was
wondering it is possible to do it manually.


--
Pierre
------------------------------------------------------------------------
Pierre's Profile: http://www.excelforum.com/member.php...fo&userid=3754
View this thread: http://www.excelforum.com/showthread...hreadid=396828

Select your range.
insert|name|Define
In the Names in workbook box, include the sheetname:
sheet1!myList
or
'sheet 1'!myList

You can have multiple worksheets each with a name of MyList.

====
If you go back to that insert|name dialog, you'll see your names--the names that
include the worksheet name to the right hand side are sheet level names.

If you work with names, do yourself a favor and 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

Jan Karel has an option in that addin that will allow you "globalize" the name
or "localize" it.



Pierre wrote:

Hi all,
In a macro I use a named range. I want to use the same macro in similar
sheets in the same workbook. But each time I name the same range in
another sheet, the first one is canceled. How can I create the name in
all the sheet ?

It is probably possible as when you copy a sheet in the same workbook,
you have the same name in all the sheets.

Thanks
:rolleyes: :rolleyes: :confused:

--
Pierre
------------------------------------------------------------------------
Pierre's Profile: http://www.excelforum.com/member.php...fo&userid=3754
View this thread: http://www.excelforum.com/showthread...hreadid=396828

--

Dave Peterson

Don't give up on your original question.

Pierre wrote:

Thanks a lot Dave.

You are right I can use the worksheet.name & RangeName.
Actually, that's what I did but as when copying the sheet in the same
workbook you generate the same named range in both sheet I was
wondering it is possible to do it manually.

--
Pierre
------------------------------------------------------------------------
Pierre's Profile: http://www.excelforum.com/member.php...fo&userid=3754
View this thread: http://www.excelforum.com/showthread...hreadid=396828

--

Dave Peterson

Thanks a lot Dave.
It works perfectly...


--
Pierre
------------------------------------------------------------------------
Pierre's Profile: http://www.excelforum.com/member.php...fo&userid=3754
View this thread: http://www.excelforum.com/showthread...hreadid=396828