You should be able to use the Areas collection of the
selected range(s) to deal with each separate 'block' of
contiguous cells. I will assume you have selected the
desired ranges, and you want to move them all to the sheet
called "NewSheet":

Dim xlRange as Range, strAddress as String

For Each xlRange in Selection.Areas
strAddress = xlRange.Address
xlRange.Copy
Sheets("NewSheet").Range(strAddress).PasteSpecial
Next xlRange

Technically you are copying and pasting one range at a
time (which you wanted to avoid), but VBA takes care of
this nicely and the code is pretty compact.

Hope this does the trick for you.

K Dales

-----Original Message-----
I have a range, D10:O12, D20:O22, and I would like to be
able to copy
the range and paste it to another sheet in the same exact
cells that I
copied it from. So far, I've unioned the ranges together
and I've tried
using .Copy method but that doesn't allow me to copy
multiple ranges.
Is there any way i can do this without having to write
code to
explicitly state where each range goes? because in
actuality, i have
about 30 different ranges I need to perform this action
on.

On a side note, i've noticed and been told that
copying/pasting two
different ranges from worksheet to another at the same
time does not
preserve the spacing between the ranges in the first
place. so
hopefully there's some way to do this in VBA without
having to copy and
paste one range at a time.


---
Message posted from http://www.ExcelForum.com/

.