Macro to copy and paste alternating cells

[DDR]

Hi All,

I have a need to create a Macro that copies alternating cells until end of data.

In my scenario I will end up with 2 sheets, each containing x number of rows (with the first row being a header row).

Row 1 in Sheet 1 has a relation to Row 1 in Sheet 2.
Row 2 in Sheet 1 has a relation to Row 2 in Sheet 2.
Row 3 in Sheet 1 has a relation to Row 3 in Sheet 2.
Row 4 in Sheet 1 has a relation to Row 4 in Sheet 2.

and so on...

What I am trying to achieve here is a Macro that copies:

Sheet 1 Cell AA2 -> Sheet3 Cell A2
Sheet 2 Cell P2 -> Sheet3 Cell A3

...Sheet 1 Cell AA3 -> Sheet3 Cell A4
...Sheet 2 Cell P3 -> Sheet3 Cell A5
...Sheet 1 Cell AA4 -> Sheet3 Cell A6
...Sheet 2 Cell P4 -> Sheet3 Cell A7
...Sheet 1 Cell AA5 -> Sheet3 Cell A8
...Sheet 2 Cell P5 -> Sheet3 Cell A9

etc;

and then keeps copying and pasting in that order and stops when Sheet 2 Column P = "" (blank).

I would be most grateful for any assistance that can be rendered.

[arlu1201]

It will be good if you attach a sample file.

[StevenM]

This is a bit crude. There is no checking that sheet 3 column A is empty.

Please Login or Register  to view this content.

[DDR]

Hi Arlette,

I have attached an example sheet.

Rather than populate with a ton of data, I've populated with some on sheet 1 and sheet 2.

If we use Sheet one column C (as the real sheet 1 column AA) and sheet 2 column B (as the real sheet 2 column P), the same effect would be achieved by copying and pasting:

Sheet1.C2 -> Sheet3.A1
Sheet2.B2 -> Sheet3.A2
Sheet1.C3 -> Sheet3.A3
Sheet2.B3 -> Sheet3.A4
Sheet1.C4 -> Sheet3.A5
Sheet2.B4 -> Sheet3.A6

And so on until Sheet2 Column B contains an empty value.

In the example sheet, Column A would end up reading:

Jump
One
Sit
Seven
Sleep
Nine
Run
Three


Many thanks for your assistance.

[DDR]

Thanks StevenM.

Also thanks Arlette for taking the time to look at my post.

[arlu1201]

Use this code. Change the cell references as required.

Please Login or Register  to view this content.