| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Getting values from names by reference | Excel Discussion (Misc queries) | |||
| COPYING FORMULA CONTAINING NAMES/RELATIVE RANGE NAMES | Excel Discussion (Misc queries) | |||
| automatic range - named range give me circular reference... | Excel Discussion (Misc queries) | |||
| reference to range names | Excel Discussion (Misc queries) | |||
| Selecting range in list of range names depending on a cell informa | Excel Discussion (Misc queries) | |||
Linear Mode
