Using single range name across sheets

[Brian.Crawford]

I have multiple worksheets where I want to access a named range using VBA. I want the range not to be sheet dependent (just for ex. the $A$1:$Q$1 part) as I will control which sheet that I use that range on with my VBA code. My code works when on the sheet that the ranges were origianlly defined on but not properly on the other sheets
(for ex. the range is set to mySheet!$G$1:$R$1) ie such as Worksheets(mySheet).Range("myDefinedRange") where "myDefinedRange" is not defined on "mySheet". The range shifts left by 1 column on the

Name Manager does not allow me to define a range without the sheet identifier. I want the definition to only be on one sheet as the number of sheets can change dynamically (new sheets added automatically) and I don't want to be naming ranges manually afterwards. I need these sheets to accomodate column & row insertions so need the automatic features of named ranges.

Sorry if this explaination is not that clear.
thanks in advance

 

[freesurf]

Hi Brian

Do you really need to create the range name on each worksheet - or do you actually require a way of dynamically being able to target the same range on all worksheets?

If it's the latter, you can use your code to build a string that adopts whichever sheetname you're currently working with.

Your only remaining challenge then is to convert this string to a range.The following link shows how you can coerce the string as a range:
http://www.vbaexpress.com/forum/archive/index.php/t-23114.html

 

[Andrew Poulsom]

You can define a sheet level name like Sheet1!MyRange. If you copy the sheet the name will also be copied.

 

[Brian.Crawford]

I'm not sure my question was very clear before. To simplify
Using name Manager, when I define a range (it seems to default to workbook vs a specific worksheet) such as

X=mysheet1!$A$1
then I use
myvalue=range("X")

if I want to use "X" to refer to the same range on another sheet can I simply use
myvalue= worksheets("mysheet2").range("X")

What is the difference when name manger (or I) defines the range as WorkBook vs specifc Worksheet when in fact it still points to a specific sheet in the actual range part
thanks all

 

[shg]

All named ranges refer to specific cells on a specific sheet. Names with workbook scope must be unique throughout the workbook; there can only be one range (or formula) named myRange.

Names with worksheet scope must likewise be unique on each worksheet. They can refer to ranges on that sheet or elsewhere.

See if stepping thorough the code below helps:

Sub x()
    Const sName     As String = "myRange"
    Dim r           As Range
 
    ' workbook scope, range is on Sheet1
    ThisWorkbook.Names.Add Name:=sName, RefersTo:=Sheet1.Range("A1")
 
    ' Sheet2 scope, but range is on Sheet1
    Sheet2.Names.Add Name:=sName, RefersTo:=Sheet1.Range("B2")
 
    ' Sheet3 scope, range is on Sheet3
    Sheet3.Names.Add Name:=sName, RefersTo:=Sheet3.Range("C3")
 
1   ' this is fine, and gets the range with workbook scope
    Set r = ThisWorkbook.Names(sName).RefersToRange
    Debug.Print r.Address(External:=True)
 
2   ' this is fine, and gets the range with Sheet2 scope
    Set r = Sheet2.Names(sName).RefersToRange
    Debug.Print r.Address(External:=True)
 
3   ' this is fine, and gets the range with Sheet3 scope
    Set r = Sheet3.Names(sName).RefersToRange
    Debug.Print r.Address(External:=True)
 
4   ' this will error because Sheet1 has no name myRange with sheet scope:
    Set r = Sheet1.Names(sName).RefersToRange
 
5   ' this will error because myName on Sheet2 refers to a range on Sheet1
    Set r = Sheet2.Range(sName)
 
6   ' this works, but is bad practice for the potential error above
    Set r = Sheet3.Range(sName)
    Debug.Print r.Address(External:=True)
End Sub