how to avoid merging when pasting multiple ranges

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

Stian,

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

HTH,
Bernie
MS Excel MVP


"Stian" <Stian@discussions.microsoft.com> wrote in message
news:551168F6-569E-4DB7-BEEB-7BC8CFFA8D3F@microsoft.com...
> 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

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


"Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
news:eLx0CmTXFHA.796@TK2MSFTNGP09.phx.gbl...
> Stian,
>
> Range("A1:B2").Copy ActiveCell
> Range("A4:B9").Copy ActiveCell(4)
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> "Stian" <Stian@discussions.microsoft.com> wrote in message
> news:551168F6-569E-4DB7-BEEB-7BC8CFFA8D3F@microsoft.com...
> > 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
>
>

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

> Stian,
>
> Range("A1:B2").Copy ActiveCell
> Range("A4:B9").Copy ActiveCell(4)
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> "Stian" <Stian@discussions.microsoft.com> wrote in message
> news:551168F6-569E-4DB7-BEEB-7BC8CFFA8D3F@microsoft.com...
> > 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:B5").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
>
>
>

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


"Stian" <Stian@discussions.microsoft.com> wrote in message
news:26417D2F-DA9D-4654-90D5-754F86DDD403@microsoft.com...
> 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" wrote:
>
> > Stian,
> >
> > Range("A1:B2").Copy ActiveCell
> > Range("A4:B9").Copy ActiveCell(4)
> >
> > HTH,
> > Bernie
> > MS Excel MVP
> >
> >
> > "Stian" <Stian@discussions.microsoft.com> wrote in message
> > news:551168F6-569E-4DB7-BEEB-7BC8CFFA8D3F@microsoft.com...
> > > 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:B5").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
> >
> >
> >

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

> 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
>
>
> "Stian" <Stian@discussions.microsoft.com> wrote in message
> news:26417D2F-DA9D-4654-90D5-754F86DDD403@microsoft.com...
> > 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" wrote:
> >
> > > Stian,
> > >
> > > Range("A1:B2").Copy ActiveCell
> > > Range("A4:B9").Copy ActiveCell(4)
> > >
> > > HTH,
> > > Bernie
> > > MS Excel MVP
> > >
> > >
> > > "Stian" <Stian@discussions.microsoft.com> wrote in message
> > > news:551168F6-569E-4DB7-BEEB-7BC8CFFA8D3F@microsoft.com...
> > > > 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:B5").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
> > >
> > >
> > >
>
>
>

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




"Stian" <Stian@discussions.microsoft.com> wrote in message
news:9A5B36E7-0BAD-49D7-8231-05B5EE73C374@microsoft.com...
> 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" wrote:
>
> > 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
> >
> >
> > "Stian" <Stian@discussions.microsoft.com> wrote in message
> > news:26417D2F-DA9D-4654-90D5-754F86DDD403@microsoft.com...
> > > 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" wrote:
> > >
> > > > Stian,
> > > >
> > > > Range("A1:B2").Copy ActiveCell
> > > > Range("A4:B9").Copy ActiveCell(4)
> > > >
> > > > HTH,
> > > > Bernie
> > > > MS Excel MVP
> > > >
> > > >
> > > > "Stian" <Stian@discussions.microsoft.com> wrote in message
> > > > news:551168F6-569E-4DB7-BEEB-7BC8CFFA8D3F@microsoft.com...
> > > > > 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:B5").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
> > > >
> > > >
> > > >
> >
> >
> >