Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Craetion of a Range Name Specific to a single sheet
How is this done?
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Craetion of a Range Name Specific to a single sheet
When naming the range name, precede it with the sheet name, such as
Sheet1!myName or 'Master Sheet1!myName -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Jim May" wrote in message ... How is this done? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Craetion of a Range Name Specific to a single sheet
Just a typo:
'Master Sheet1!myName should be: 'Master Sheet1'!myName (an additional apostrophe) Bob Phillips wrote: When naming the range name, precede it with the sheet name, such as Sheet1!myName or 'Master Sheet1!myName -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Jim May" wrote in message ... How is this done? -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Craetion of a Range Name Specific to a single sheet
Thanks Bob (and of course Dave..) -- This helps me to progress
through things - after following your instructions I "look-back" and see that in creating such a sheet-specific range-name - it once created is only visible from the "home" sheetname, and not the others. If In a VBA routine I were to Refer to these rangenames do I need to do anything SPECIAL to Read them (being Sheet Specific)? Thanks for your help.. Jim "Dave Peterson" wrote: Just a typo: 'Master Sheet1!myName should be: 'Master Sheet1'!myName (an additional apostrophe) Bob Phillips wrote: When naming the range name, precede it with the sheet name, such as Sheet1!myName or 'Master Sheet1!myName -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Jim May" wrote in message ... How is this done? -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Craetion of a Range Name Specific to a single sheet
You can use:
dim myVal1 as variant dim myVal2 as variant myval1 = worksheets("sheet1").range("test1").value myval2 = worksheets("sheet2").range("test1").value Same range name on different sheets--but fully qualified. Jim May wrote: Thanks Bob (and of course Dave..) -- This helps me to progress through things - after following your instructions I "look-back" and see that in creating such a sheet-specific range-name - it once created is only visible from the "home" sheetname, and not the others. If In a VBA routine I were to Refer to these rangenames do I need to do anything SPECIAL to Read them (being Sheet Specific)? Thanks for your help.. Jim "Dave Peterson" wrote: Just a typo: 'Master Sheet1!myName should be: 'Master Sheet1'!myName (an additional apostrophe) Bob Phillips wrote: When naming the range name, precede it with the sheet name, such as Sheet1!myName or 'Master Sheet1!myName -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Jim May" wrote in message ... How is this done? -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Craetion of a Range Name Specific to a single sheet
Are you saying that If I were to use:
myval1 = range("test1").value myval2 = range("test1").value That there might be a problem with myval1 and myval2 ? Jim "Dave Peterson" wrote: You can use: dim myVal1 as variant dim myVal2 as variant myval1 = worksheets("sheet1").range("test1").value myval2 = worksheets("sheet2").range("test1").value Same range name on different sheets--but fully qualified. Jim May wrote: Thanks Bob (and of course Dave..) -- This helps me to progress through things - after following your instructions I "look-back" and see that in creating such a sheet-specific range-name - it once created is only visible from the "home" sheetname, and not the others. If In a VBA routine I were to Refer to these rangenames do I need to do anything SPECIAL to Read them (being Sheet Specific)? Thanks for your help.. Jim "Dave Peterson" wrote: Just a typo: 'Master Sheet1!myName should be: 'Master Sheet1'!myName (an additional apostrophe) Bob Phillips wrote: When naming the range name, precede it with the sheet name, such as Sheet1!myName or 'Master Sheet1!myName -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Jim May" wrote in message ... How is this done? -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Craetion of a Range Name Specific to a single sheet
Nope. No problem with the code at all.
But if the code is in a General module and that name is a local name used on the activesheet, then myVal1 and myVal2 will refer to the same range (Test1) on the Activesheet. If you want to retrieve the values from two different ranges on different sheets that have the same name, then you'll want to qualify each with the correct worksheet. Excel is pretty forgiving. If you have a line like: msgbox Range("test1").address(external:=true) And you have a global range named Test1, you may even get the results you want--depending on where the code is and what sheet is active and if there is a sheet level name on that activesheet. But I've found that lots of problems go away by qualifying the range with the worksheet. Do some experiments: Create a test workbook with 3 worksheets. Put a workbook level name (test1) on sheet1 put a worksheet level name (test1) on sheet2 don't put any names on sheet3. Put this code behind all 3 worksheets: Option Explicit Sub testme() MsgBox Range("test1").Address(external:=True) End Sub Run each procedure. Put the same code in a general module and activate each sheet and run the code (3 times total). You'll see some problems with the code and maybe see some things that you didn't expect. And if you delete the global name, you can do a few more experiments to see how the code reacts. Jim May wrote: Are you saying that If I were to use: myval1 = range("test1").value myval2 = range("test1").value That there might be a problem with myval1 and myval2 ? Jim "Dave Peterson" wrote: You can use: dim myVal1 as variant dim myVal2 as variant myval1 = worksheets("sheet1").range("test1").value myval2 = worksheets("sheet2").range("test1").value Same range name on different sheets--but fully qualified. Jim May wrote: Thanks Bob (and of course Dave..) -- This helps me to progress through things - after following your instructions I "look-back" and see that in creating such a sheet-specific range-name - it once created is only visible from the "home" sheetname, and not the others. If In a VBA routine I were to Refer to these rangenames do I need to do anything SPECIAL to Read them (being Sheet Specific)? Thanks for your help.. Jim "Dave Peterson" wrote: Just a typo: 'Master Sheet1!myName should be: 'Master Sheet1'!myName (an additional apostrophe) Bob Phillips wrote: When naming the range name, precede it with the sheet name, such as Sheet1!myName or 'Master Sheet1!myName -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Jim May" wrote in message ... How is this done? -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Craetion of a Range Name Specific to a single sheet
Take a look at http://xldynamic.com/source/xld.Names.html
-- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Jim May" wrote in message ... Thanks Bob (and of course Dave..) -- This helps me to progress through things - after following your instructions I "look-back" and see that in creating such a sheet-specific range-name - it once created is only visible from the "home" sheetname, and not the others. If In a VBA routine I were to Refer to these rangenames do I need to do anything SPECIAL to Read them (being Sheet Specific)? Thanks for your help.. Jim "Dave Peterson" wrote: Just a typo: 'Master Sheet1!myName should be: 'Master Sheet1'!myName (an additional apostrophe) Bob Phillips wrote: When naming the range name, precede it with the sheet name, such as Sheet1!myName or 'Master Sheet1!myName -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Jim May" wrote in message ... How is this done? -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
macro | Excel Discussion (Misc queries) | |||
Range name: Change from sheet specific to global | Excel Discussion (Misc queries) | |||
Match and Sort for two range of data on different worksheets? | New Users to Excel | |||
How to copy records containing a specific date range to new sheet? | Excel Worksheet Functions | |||
Hyperlink to specific sheet in Excel Web File | Links and Linking in Excel |