Reference Range Names in Macro
 

Hi, This works:

Sheets("Notes").Range("b17:K17").Copy Sheets("RouteSlip").Range("B39:K39")

But I was hoping to use range names instead of cell references and I can't
make it work.

Range b17:K17 = Audit1
Range b39:k39 = Note1

Thank you.

Reference Range Names in Macro
 

I should add that the B17:K17 and b39:k39 are merged cells. Not sure if that
matters.

"Karin" wrote:

Hi, This works:

Sheets("Notes").Range("b17:K17").Copy Sheets("RouteSlip").Range("B39:K39")

But I was hoping to use range names instead of cell references and I can't
make it work.

Range b17:K17 = Audit1
Range b39:k39 = Note1

Thank you.

Reference Range Names in Macro
 

Assuming your named ranges are globally defined then

application.names("Audit1").referstorange.copy _
Destination:=application.names("Note1").referstora nge
--
HTH...

Jim Thomlinson


"Karin" wrote:

Hi, This works:

Sheets("Notes").Range("b17:K17").Copy Sheets("RouteSlip").Range("B39:K39")

But I was hoping to use range names instead of cell references and I can't
make it work.

Range b17:K17 = Audit1
Range b39:k39 = Note1

Thank you.

Reference Range Names in Macro
 

Hi, Jim, thank you.
It didn't work, perhaps I'm not globally defined - ranges were named in the
name box, sheet that Audit1 is on is hidden.

"Jim Thomlinson" wrote:

Assuming your named ranges are globally defined then

application.names("Audit1").referstorange.copy _
Destination:=application.names("Note1").referstora nge
--
HTH...

Jim Thomlinson


"Karin" wrote:

Hi, This works:

Sheets("Notes").Range("b17:K17").Copy Sheets("RouteSlip").Range("B39:K39")

But I was hoping to use range names instead of cell references and I can't
make it work.

Range b17:K17 = Audit1
Range b39:k39 = Note1

Thank you.

Reference Range Names in Macro
 

If they are locally declared then...

Sheets("Notes").Range("Audit1").Copy Sheets("RouteSlip").Range("Note1")
--
HTH...

Jim Thomlinson


"Karin" wrote:

Hi, Jim, thank you.
It didn't work, perhaps I'm not globally defined - ranges were named in the
name box, sheet that Audit1 is on is hidden.

"Jim Thomlinson" wrote:

Assuming your named ranges are globally defined then

application.names("Audit1").referstorange.copy _
Destination:=application.names("Note1").referstora nge
--
HTH...

Jim Thomlinson


"Karin" wrote:

Hi, This works:

Sheets("Notes").Range("b17:K17").Copy Sheets("RouteSlip").Range("B39:K39")

But I was hoping to use range names instead of cell references and I can't
make it work.

Range b17:K17 = Audit1
Range b39:k39 = Note1

Thank you.

Reference Range Names in Macro
 

Merged cells are a pain...

Can you just assign the value?

WorkSheets("RouteSlip").Range("Note1").Value _
= WorkSheets("Notes").Range("Audit1")



Karin wrote:

Hi, This works:

Sheets("Notes").Range("b17:K17").Copy Sheets("RouteSlip").Range("B39:K39")

But I was hoping to use range names instead of cell references and I can't
make it work.

Range b17:K17 = Audit1
Range b39:k39 = Note1

Thank you.

--

Dave Peterson

Reference Range Names in Macro
 

The named ranges are defined currently?

Audit1 refers to =Notes!$B$17:$K$17

Notel refers to =RouteSlip!$B$39:$K$39

No need to paste to a range.

Top left cell only will do.

Sheets("Notes").Range("Audit1").Copy Sheets("RouteSlip").Range("B39")

But you can use "Notel" if you want to.


Gord Dibben MS Excel MVP

On Wed, 26 Aug 2009 12:30:03 -0700, Karin
wrote:

Hi, This works:

Sheets("Notes").Range("b17:K17").Copy Sheets("RouteSlip").Range("B39:K39")

But I was hoping to use range names instead of cell references and I can't
make it work.

Range b17:K17 = Audit1
Range b39:k39 = Note1

Thank you.

Reference Range Names in Macro
 

Thank you, but for some reason it doesn't work. However, the answer below
from Dave Peterson does work.

"Jim Thomlinson" wrote:

If they are locally declared then...

Sheets("Notes").Range("Audit1").Copy Sheets("RouteSlip").Range("Note1")
--
HTH...

Jim Thomlinson


"Karin" wrote:

Hi, Jim, thank you.
It didn't work, perhaps I'm not globally defined - ranges were named in the
name box, sheet that Audit1 is on is hidden.

"Jim Thomlinson" wrote:

Assuming your named ranges are globally defined then

application.names("Audit1").referstorange.copy _
Destination:=application.names("Note1").referstora nge
--
HTH...

Jim Thomlinson


"Karin" wrote:

Hi, This works:

Sheets("Notes").Range("b17:K17").Copy Sheets("RouteSlip").Range("B39:K39")

But I was hoping to use range names instead of cell references and I can't
make it work.

Range b17:K17 = Audit1
Range b39:k39 = Note1

Thank you.

Reference Range Names in Macro
 

Thank you -works great.


"Dave Peterson" wrote:

Merged cells are a pain...

Can you just assign the value?

WorkSheets("RouteSlip").Range("Note1").Value _
= WorkSheets("Notes").Range("Audit1")



Karin wrote:

Hi, This works:

Sheets("Notes").Range("b17:K17").Copy Sheets("RouteSlip").Range("B39:K39")

But I was hoping to use range names instead of cell references and I can't
make it work.

Range b17:K17 = Audit1
Range b39:k39 = Note1

Thank you.

--

Dave Peterson