Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 -- Pierre ------------------------------------------------------------------------ Pierre's Profile: http://www.excelforum.com/member.php...fo&userid=3754 View this thread: http://www.excelforum.com/showthread...hreadid=396828 |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using a relative SHEET reference for source data in a chart | Charts and Charting in Excel | |||
requires that merged cells must be identically sized? | Excel Discussion (Misc queries) | |||
Convert data of cells to any type: Number, Date&Time, Text | Excel Discussion (Misc queries) | |||
how can i select all the cells with same color on a sheet if there are multipale colors by vba code | Charts and Charting in Excel | |||
3 cells are named - how to refere to them in one reference field in a chart | Charts and Charting in Excel |