how do i paste a range?

  • Thread starter Thread starter majikman
  • Start date Start date
M

majikman

I have a range, D10:O12, D20:O22, and I would like to be able to cop
the range and paste it to another sheet in the same exact cells that
copied it from. So far, I've unioned the ranges together and I've trie
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 t
explicitly state where each range goes? because in actuality, i hav
about 30 different ranges I need to perform this action on.

On a side note, i've noticed and been told that copying/pasting tw
different ranges from worksheet to another at the same time does no
preserve the spacing between the ranges in the first place. s
hopefully there's some way to do this in VBA without having to copy an
paste one range at a time
 
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
 
Back
Top