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:

2024-06-15_180556.jpg

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

2024-06-15_181104.jpg

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:

2024-06-15_181419.jpg

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

2024-06-15_181624.jpg

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:

2024-06-15_182054.jpg

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:

2024-06-15_182913.jpg

[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)

2024-06-16_111648.jpg

[MasterBash]

That is what I ended up doing on another sheet.

Thank you !