Using a named cell in a macro

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Pa Maher - 18 Sep 2007 21:28 GMT

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.

Reply to this Message

JE McGimpsey - 19 Sep 2007 01:59 GMT

> 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")

Reply to this Message

Bob Greenblatt - 19 Sep 2007 13:00 GMT

On 9/18/07 8:59 PM, in article
jemcgimpsey-0A4392.18591118092007@msnews.microsoft.com, "JE McGimpsey"
<jemcgimpsey@mvps.org> wrote:

>> Currently in a macro, I copy a cell from Workbook A into Workbook B by
>> specifying the worksheet and cell ID.
[quoted text clipped - 17 lines]
> 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]

Signature

Bob Greenblatt [MVP], Macintosh
bobgreenblattATmsnDOTcom

Reply to this Message

Mac Forum / Applications / Excel / September 2007

Using a named cell in a macro