same Named cells in different sheet

[Pierre]

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

[Dave O]

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.

[Pierre]

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.

[Dave Peterson]

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
>
>
> --
> 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

[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

[Pierre]

Thanks a lot Dave.
It works perfectly...