Copy Multiple Selections thru Code

[Dee Veloper]

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

 

[Jim May]

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

 

[Ron de Bruin]

Hi Dee

You can use Areas

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

 

[Ron de Bruin]

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

 

[Dee Veloper]

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.

 

[Myrna Larson]

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

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" <[email protected]> wrote in message

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

 

[Dee Veloper]

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.

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

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" <[email protected]> wrote in message

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

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


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