vba - select and copy multiple rows in different columns

[MasterBash]

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.


Sub button1()


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

Selection.Copy

End Sub


or


Sub button1()


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

Selection.Copy
End Sub


or


Sub button1()


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


End Sub



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

[p45cal]

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:

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:

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

31645

and you can copy those four areas at once to somewhere else (F14 in this case) with
Range("A14:A18,D14:D18,D21:D23,A21:A23").Copy Range("F14") resulting in:

31646

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

31647

In general, it's better to copy those areas one at a time by looping through them eg.:
For Each are In Range("A32:A36,A39:A41,D32:D35,D38:D41").Areas
You could use that like say:
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:

31648

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

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:

31649

[MasterBash]

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



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.

[p45cal]

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)

31650

[MasterBash]

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

Thank you !