Global named range ????

[Susan Lammi]

Is it possible to create a named range that will refer to the same cell on
every worksheet in a particular workbook

ie

SheetType=ActiveSheet!$A$1

This does not seem to work

Thanks

 

[Susan Lammi]

I have set the named range as you suggested....

Now I am trying to access the value in VBA

Activesheet.Range("SheetType").................. this works

Worksheets("sheetname").Range("SheetType") ...............this does not

can refer to the range on a specific sheet this way or must I set the
activesheet and refer to it that way????

Thanks for your help

 

[Dave Peterson]

I don't think that this Name is a range name. But I could do this:

Option Explicit
Sub testme()

Dim wks As Worksheet

With ActiveWorkbook
.Names.Add Name:="SheetType", RefersToR1C1:="=!r1c1", _
Visible:=True

For Each wks In .Worksheets
MsgBox wks.Evaluate(Mid(.Names("sheettype"), 3))
Next wks
End With

End Sub

The .names.add is the same as:
Insert|Name|Define
SheetType (in the name box)
=!$A$1 (in the refers to box)

the wks.evaluate returns the address contained in the name (after dumping the
"=!"). The wks.evaluate evaluates that address from the worksheet's
perspective.

But I don't think I'd do this. This range always refers to A1--no matter if you
move the cell or if you delete/insert rows or columns. It's very similar to the
worksheet function =indirect("a1")

Alternatively, you could define one range on one worksheet and use its address.

Option Explicit
Sub testme02()
Dim wks As Worksheet
Dim myAddr As String

Worksheets("sheet1").Range("a1").Name = "sheettype"
myAddr = Worksheets("sheet1").Range("a1").Address(0, 0)

For Each wks In ActiveWorkbook.Worksheets
MsgBox wks.Range(myAddr).Address(external:=True)
Next wks
End Sub

I'm not sure what you're doing, but why not just use the address directly: