how to avoid merging when pasting multiple ranges

[Guest]

Hi,

I have written a simple macro that copies multiple ranges and then pastes
them into the active cell. The problem is that the ranges are automatically
merged, while I would like them to keep their original postition in relation
to each other.

Example:

A B C D E F G
1 2 5
2 3 5
3
4 2 3
5 1 9

In this example I want to copy the two ranges A1:B2 and A4:B9, and paste
them into my active cell which here would be F2, using this simple code:

Range("A1:B2, A4:B9").Copy
ActiveCell.Paste

This is what I get:

A B C D E F G
1 2 5 2 5
2 3 5 3 5
3 2 3
4 2 3 1 9
5 1 9


This is what I want:

A B C D E F G
1 2 5 2 5
2 3 5 3 5
3
4 2 3 2 3
5 1 9 1 9


Does anyone know how to avoid the merging of the ranges?

Any help would be greatly appreciated. The problem is small. The
implications are big...

Regards
Stian

 

[Bernie Deitrick]

Stian,

Range("A1:B2").Copy ActiveCell
Range("A4:B9").Copy ActiveCell(4)

HTH,
Bernie
MS Excel MVP

 

[Bernie Deitrick]

Stian,

To generalize the code, you could use something like:

Dim R1 As Range
Dim R2 As Range

Set R1 = Range("A1:B2")
Set R2 = Range("B4:C9")
R1.Copy ActiveCell
R2.Copy ActiveCell(R2(1).Row - R1(1).Row + 1, _
R2(1).Column - R1(1).Column + 1)

HTH,
Bernie
MS Excel MVP

 

[Guest]

Hi Bernie,

Thanks for providing feedback, but I do not understand what you mean.
Are you suggesting I copy the first range first, and then the second?
I do not understand why ActiveCell is a part of the copy procedure.

What I am trying to do is copying several ranges that are not connected to
each other in one operation, and then pasting these ranges into the active
cell without having the ranges merging together.

Please reply again if you can.

Regards
Stian

 

[Bernie Deitrick]

Stian,

Are you suggesting I copy the first range first, and then the second? Yes.

I do not understand why ActiveCell is a part of the copy procedure.

The activecell is where you want the paste to occur, no?
At least, that is what your example code had.

Did you actually try the code that I posted?

HTH,
Bernie
MS Excel MVP

 

[Guest]

Hi Bernie,

Thanks for replying back.

I understand your code now and it works perfectly.
I did not understand that you copied directly to the destination, but I get
it now.

However, I was not accurate enough in my description. I actually need to do
this in two operations, where the user will use one macro button to copy the
ranges, then go to another sheet, activate the right cell, and then use
another macro button to paste the ranges onto this sheet. It is in this last
operation that the ranges will merge. Is there any way to copy the ranges one
by one without only keeping the last one, and then maybe i can specify how
many rows from the ActiveCell each range should be copied into(like you did
with Range("B2:B9").Copy ActiveCell(4))?

I appreciate your help so far Bernie.
Feel free to reply back again

Regards
Stian

 

[Bernie Deitrick]

Stian,

Copy the entire block of code to a codemodule.

Assign the first macro to your first button, and the second to the other
macro button.

HTH,
Bernie
MS Excel MVP

Dim mySelect As Range

Sub Sub1()
Set mySelect = Selection
MsgBox "OK, now select the destination range."
End Sub

Sub Sub2()
Dim R1 As Range
Dim R2 As Range
Dim myTarget As Range
Dim i As Integer

Application.CutCopyMode = False
Set myTarget = Selection(1)

Set R1 = mySelect.Areas(1)

For i = 1 To mySelect.Areas.Count
Set R2 = mySelect.Areas(i)
R2.Copy myTarget(R2(1).Row - R1(1).Row + 1, _
R2(1).Column - R1(1).Column + 1)
Next i

End Sub