vba - select and copy multiple rows in different columns

Hello,

I tried 3 different ways of selecting and copying data that are in different rows/columns. They all give me error 400, unless they are in the same columns.

Code:

---

Sub button1()


Application.Union(Range("B14", "B18"), Range("A21", "A24")).Select

Selection.Copy

End Sub

---

or

Code:

---

Sub button1()


'Application.Intersect(Range("B14", "B18"), Range("A21", "A24")).Select

Selection.Copy
End Sub

---

or

Code:

---

Sub button1()


Range("B14:B18, A21:A24").Select
Selection.Copy


End Sub

---

Is there a way to accomplish what I am trying to do ?

Quote:

---

Originally Posted by MasterBash

I tried 3 different ways of selecting and copying data that are in different rows/columns. They all give me error 400, unless they are in the same columns.

---

I get this:

Attachment 31644

which is not strictly accurate. If the areas you select can condense to a neat rectangular range (with a few other conditions) you can copy multiple selections at once. This works:

Code:

---

Range("A14:A18,D14:D18,D21:D23,A21:A23").Copy

---

Attachment 31645

and you can copy those four areas at once to somewhere else (F14 in this case) with

Code:

---

Range("A14:A18,D14:D18,D21:D23,A21:A23").Copy  Range("F14")

---

resulting in:

Attachment 31646

Trying to copy the yellow highlighted area below won't work however, even though it can be resolved to a single rectangular range:

Attachment 31647

In general, it's better to copy those areas one at a time by looping through them eg.:

Code:

---

For Each are In Range("A32:A36,A39:A41,D32:D35,D38:D41").Areas

---

You could use that like say:

Code:

---

Set destn = Range("F32")
For Each are In Range("A32:A36,A39:A41,D32:D35,D38:D41").Areas
  are.Copy destn
  Set destn = destn.Offset(are.Rows.Count)
Next are

---

which results in:

Attachment 31648

or simply by having one line per area that you want to copy:

Code:

---

Range("A32:A36").Copy Range("F32")
Range("A39:A41").Copy Range("G32")
Range("D32:D35").Copy Range("H32")
Range("D38:D41").Copy Range("I32")

---

which gives you:

Attachment 31649

I am just looking to copy them to the clipboard so I can paste in another program.

Code:

---

Set destn = Range("F32")
For Each are In Range("A32:A36,A39:A41,D32:D35,D38:D41").Areas
  are.Copy destn
  Set destn = destn.Offset(are.Rows.Count)
Next are

---

I believe this is the best option. However, I am worried about the problems this may cause if multiple people uses the worksheet. I believe people will be overwriting each other everytime.

Thank you for bringing up different options. I will probably have to do my worksheet differently.

Perhaps put links to the values you want to copy in a rectangular range somewhere (it doesn't even have to be on the same sheet) and copy that?:
(Here I'm showing the formulae rather than any values, and you'd copy E14:E22)

Attachment 31650

That is what I ended up doing on another sheet. :)

Thank you !