Using a named cell in a macro

[Pa Maher]

Currently in a macro, I copy a cell from Workbook A into Workbook B by
specifying the worksheet and cell ID.
The cell is named. I have tried using the cell name but it's not working.

 

[JE McGimpsey]

Currently in a macro, I copy a cell from Workbook A into Workbook B by
specifying the worksheet and cell ID.
The cell is named. I have tried using the cell name but it's not working.

Is the cell named on the workbook level or the sheet level?

One way:

If the name 'myname' is defined on the workbook-level:

Dim rCopy As Range
Set rCopy = Workbooks("A.xls").Names("myname").RefersToRange
rCopy.Copy _
Destination:=Workbooks("B.xls").Sheets("Sheet1").Range("A1")

Or, if it's a sheet-level name:

Dim rCopy As Range
Set rCopy = Workbooks("A.xls").Sheets("Sheet1").Range("myname")
rCopy.Copy _
Destination:=Workbooks("B.xls").Sheets("Sheet1").Range("A1")

 

[Bob Greenblatt]

Is the cell named on the workbook level or the sheet level?

One way:

If the name 'myname' is defined on the workbook-level:

Dim rCopy As Range
Set rCopy = Workbooks("A.xls").Names("myname").RefersToRange
rCopy.Copy _
Destination:=Workbooks("B.xls").Sheets("Sheet1").Range("A1")

Or, if it's a sheet-level name:

Dim rCopy As Range
Set rCopy = Workbooks("A.xls").Sheets("Sheet1").Range("myname")
rCopy.Copy _
Destination:=Workbooks("B.xls").Sheets("Sheet1").Range("A1")

Or, yet another way that doesn't care whether the name is workbook or sheet
level is:

Set rcopy=workbooks("a.xls").sheets("sheetname").[myname]