
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:
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