Copy Multiple Selections thru Code
 

Hello
i allow a user to select a range that is non-contiguous.
thru vba, i name that range (selection.name="test")

the range test might looks like this:
=Map!R3C8:R13C8,Map!R7C9,Map!R7C10,Map!R7C11

if i try to copy this range, excel gives an error message:
that command can't be used on multiple selections.

is there a way, thru vba, to copy that range to another range?

thanks for any help

Copy Multiple Selections thru Code
 

See <<below How do I print only the Cells I Need..
My offering # 1 with correction #2.
HTH

"Dee Veloper" wrote in message
om...
Hello
i allow a user to select a range that is non-contiguous.
thru vba, i name that range (selection.name="test")

the range test might looks like this:
=Map!R3C8:R13C8,Map!R7C9,Map!R7C10,Map!R7C11

if i try to copy this range, excel gives an error message:
that command can't be used on multiple selections.

is there a way, thru vba, to copy that range to another range?

thanks for any help

Copy Multiple Selections thru Code
 

Hi Dee

You can use Areas

See the example on this page
http://www.rondebruin.nl/copy1.htm#range2

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Dee Veloper" wrote in message om...
Hello
i allow a user to select a range that is non-contiguous.
thru vba, i name that range (selection.name="test")

the range test might looks like this:
=Map!R3C8:R13C8,Map!R7C9,Map!R7C10,Map!R7C11

if i try to copy this range, excel gives an error message:
that command can't be used on multiple selections.

is there a way, thru vba, to copy that range to another range?

thanks for any help

Copy Multiple Selections thru Code
 

I mean this one
http://www.rondebruin.nl/copy1.htm#selection2

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Dee Veloper" wrote in message om...
Hello
i allow a user to select a range that is non-contiguous.
thru vba, i name that range (selection.name="test")

the range test might looks like this:
=Map!R3C8:R13C8,Map!R7C9,Map!R7C10,Map!R7C11

if i try to copy this range, excel gives an error message:
that command can't be used on multiple selections.

is there a way, thru vba, to copy that range to another range?

thanks for any help

Copy Multiple Selections thru Code
 

Hi Ron and thanks

I solved the problem before i got to your post but kept your link for
future reference. it seems to have some great time-saving code example

My solution:
Sub copyToOtherLevel()
Selection.Name = "CopyFrom"
Selection.Offset([myrowcount] + 1, 0).Select
Selection.Name = "CopyTo"
Dim rCell As Range
For Each rCell In [CopyTo]
rCell.Value = Cells(rCell.Row - ([myrowcount] + 1), rCell.Column)
Next rCell
End Sub

the code above allows you to take a non-contiguous range and offset it
by the number of rows specified in a cell called [myrowcount] (the
column does not change). then each cell in the copyfrom range is
written to the copyto range.





"Ron de Bruin" wrote in message ...
I mean this one
http://www.rondebruin.nl/copy1.htm#selection2

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Dee Veloper" wrote in message om...
Hello
i allow a user to select a range that is non-contiguous.
thru vba, i name that range (selection.name="test")

the range test might looks like this:
=Map!R3C8:R13C8,Map!R7C9,Map!R7C10,Map!R7C11

if i try to copy this range, excel gives an error message:
that command can't be used on multiple selections.

is there a way, thru vba, to copy that range to another range?

thanks for any help

Copy Multiple Selections thru Code
 

Hi Myrna and thanks for the feedback

Please note that the selections are required. Without them the ranges
do not exist - these named ranges are created based on a user
selection in the worksheet.

The selection.offset is also required..in fact it is the critical
piece of code which allows a non-contiguous range be copied to the
same range x rows down. (where x is myrowcount)

As for, Range versus [ ] - that is a very interesting suggestion.
I'll have to run some benchmarks..but for the sake of this app it is
not an issue - the operation is over in a digital heartbeat.

In general though, I prefer braces because i find the code cleaner and
easier to read.

thanks for the constructive feedback.

Myrna Larson wrote in message . ..
If you are concerned with "time-saving", don't select things, and don't use
the bracket notation. [myrowcount] and [CopyTo] are much slower to evaluate
than

Range("CopyFrom") and Range("myrowCount")


On 15 Nov 2004 18:30:49 -0800, (Dee Veloper) wrote:

Hi Ron and thanks

I solved the problem before i got to your post but kept your link for
future reference. it seems to have some great time-saving code example

My solution:
Sub copyToOtherLevel()
Selection.Name = "CopyFrom"
Selection.Offset([myrowcount] + 1, 0).Select
Selection.Name = "CopyTo"
Dim rCell As Range
For Each rCell In [CopyTo]
rCell.Value = Cells(rCell.Row - ([myrowcount] + 1), rCell.Column)
Next rCell
End Sub

the code above allows you to take a non-contiguous range and offset it
by the number of rows specified in a cell called [myrowcount] (the
column does not change). then each cell in the copyfrom range is
written to the copyto range.





"Ron de Bruin" wrote in message
...
I mean this one
http://www.rondebruin.nl/copy1.htm#selection2

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Dee Veloper" wrote in message
om...
Hello
i allow a user to select a range that is non-contiguous.
thru vba, i name that range (selection.name="test")

the range test might looks like this:
=Map!R3C8:R13C8,Map!R7C9,Map!R7C10,Map!R7C11

if i try to copy this range, excel gives an error message:
that command can't be used on multiple selections.

is there a way, thru vba, to copy that range to another range?

thanks for any help