VBA -- Copy/Paste across sheets

**Rasm** · 09-20-2012, 03:44 AM

I am trying to copy data from one sheet to another - but get a runtime error 1004 --- it will not allow me to have more than a single range --- any ideas or do I have to copy/paste one range at a time

My range is i.e. "$A4:$A299,$K4:$K10"

Code:

      Worksheets(SourceSheetName).Range(RangeSource).Copy
      Worksheets(DistinationSheetName).Paste Destination:=Worksheets(DistinationSheetName).Range(RangeDestination)

**Admin** · 09-20-2012, 08:16 AM

Hi

I think the range size should be same otherwise you won't able to paste.

**Rick Rothstein** · 09-20-2012, 10:28 PM

When you copy/paste, the range must be contiguous. If you try to copy/paste non-contiguous ranges manually, you get a warning message that says "That command cannot be used on multiple selections."

If you actually want all the properties of the cells copied along with the values in the cells, you will have to loop through the range's areas (basically, an area is a contiguous sub-range within the overall non-contiguous range). Something like this...

Code:

For Each Rng In Worksheets(SourceSheetName).Range(RangeSource).Areas
   Rng.Copy {{reference to the top left cell where the sub-range is to be copied to}}
Next

I can't give you specifics for the part I enclosed in the double-curly braces because you gave us no information about RangeDestination, but you would have to calculate each top left cell in it for each sub-range you are copying. If you provide more details, we can give you the actual code you would use.

**Rasm** · 09-21-2012, 03:27 AM

**Rick Rothstein** · 09-21-2012, 02:07 PM

Originally Posted by Rasm

Thanks --- I have changed my code so it loops -- one range at a time-- but what a pain - I have to copy columns that are not contiguous -- also my destination is not the same columns --- I appreciate the offer of code. But I got it working now. I have approx 700 columns of data --- so I am considering to insert a row and then place a number in that row represent the order that I want to copy the data --- I know how to sort rows -- But have never sorted columns. I will make a separate post asking how to sort on rows

If you provide the details of your setup, what ranges comprise your non-contiguous areas, why/how you identify them, what you are doing with them, etc., etc., then perhaps we can offer some more condensed coding than it sounds like you may have currently. Also posting the code you have now, in conjunction with the above details, might help us help you further as well. VB coding can do marvelous things, and in a lot of cases, in sometimes surprisingly compact code procedures, but for anyone to help you to do that, we need details.