How to name a range in a different workbook?

[keithb]

How can I name a range that exists in a different workbook?

Thanks,

Keith

 

[Dave Peterson]

I'd open both workbooks.

Activate the workbook that's gonna get the name.

Then
Insert|Name|Define
Type in your name in the "Names in workbook" box
click in the "refers to" box (I'd erase what's there)
click on Window, then point at the other workbook.
go to the worksheet you want to use
select that range

I see something like:
=[book1.xls]Sheet3!$B$9:$B$19
in that "refers to" box.

 

[keithb]

Thanks for answering. Actually, I need to do this with VBA code rather than
from the user interface. Any idea how to do that?

Thanks,

Keith

I'd open both workbooks.

Activate the workbook that's gonna get the name.

Then
Insert|Name|Define
Type in your name in the "Names in workbook" box
click in the "refers to" box (I'd erase what's there)
click on Window, then point at the other workbook.
go to the worksheet you want to use
select that range

I see something like:
=[book1.xls]Sheet3!$B$9:$B$19
in that "refers to" box.

How can I name a range that exists in a different workbook?

Thanks,

Keith

 

[Norman Jones]

Hi Keith,

Try something like:

Sub Tester09()
Dim WB As Workbook
Dim WS As Worksheet
Dim Rng As Range


Set WB = Workbooks("ABC.xls") '<<==== CHANGE
Set WS = WB.Sheets("Sheet1") '<<==== CHANGE
Set Rng = WS.Range("A1100") '<<==== CHANGE

Rng.Name = "Eureka" '<<==== CHANGE

End Sub

 

[Dave Peterson]

If I'm not sure what the code should look like, I use the macro recorder when I
do it manually.

This is what I got when I recorded one:

ActiveWorkbook.Names.Add Name:="asdfasdf", RefersToR1C1:= _
"=[book1.xls]Sheet1!R1C1:R8C1"

Thanks for answering. Actually, I need to do this with VBA code rather than
from the user interface. Any idea how to do that?

Thanks,

Keith

I'd open both workbooks.

Activate the workbook that's gonna get the name.

Then
Insert|Name|Define
Type in your name in the "Names in workbook" box
click in the "refers to" box (I'd erase what's there)
click on Window, then point at the other workbook.
go to the worksheet you want to use
select that range

I see something like:
=[book1.xls]Sheet3!$B$9:$B$19
in that "refers to" box.

How can I name a range that exists in a different workbook?

Thanks,

Keith