Copying alternating rows from one sheet to another (Weaving)

[masterelaichi]

Hi all,

Is there a way to "weave" rows from one excel sheet to another? By weaving, I mean copying alternating excel rows. I have two different workbooks with monthly reports. Both reports have the same number columns

The workbook contains the progress of certain projects in the organisation. The reason behind this is so that I can compare two rows for the same project, i.e data from previous month vs current month

For example, I have two sheets, Source Sheet and Destination Sheet

Source Sheet (previous month) has rows:

1A
1B
1C
1D

Destination Sheet (current month) has rows:

2A
2B
2C
2D
2E
2F
(2A and 2F are new projects added in the current month)

I want a resulting sheet that looks like below:

2A
1A
2B
1B
2C
1C
2D
1D
2E
2F

Is there a way to do this without having to manually copy the rows?

Thanks in advance

 

[JLGWhiz]

Hi all,

Is there a way to "weave" rows from one excel sheet to another? By weaving, I mean copying alternating excel rows. I have two different workbooks with monthly reports. Both reports have the same number columns

The workbook contains the progress of certain projects in the organisation. The reason behind this is so that I can compare two rows for the same project, i.e data from previous month vs current month

For example, I have two sheets, Source Sheet and Destination Sheet

Source Sheet (previous month) has rows:

1A
1B
1C
1D

Destination Sheet (current month) has rows:

2A
2B
2C
2D
2E
2F
(2A and 2F are new projects added in the current month)

I want a resulting sheet that looks like below:

2A
1A
2B
1B
2C
1C
2D
1D
2E
2F

Is there a way to do this without having to manually copy the rows?

Thanks in advance

Maybe you can work with this to do what you want. As you can see by the comments in the code, There was incomplete information in the OP to write the code for your specific setup, but this is a generic model and can be made to work by filling in things like sheet names where I used index numbers, since the index is subject to change. Also, I used column A to match data from one sheet to another and that may not be the best column to use, but I wouldn't know since that info was not provided in the post.

Sub copyPrev()
Dim sh1 As Worksheet, sh2 As Worksheet, lr As Long, fn As Range
Set sh1 = Sheets(1) 'Previous Month-Use actual sheet name
Set sh2 = Sheets(2) 'Current Month-Use actual sheet name
lr = sh2.Cells(Rows.Count, 1).End(xlUp).Row
    For i = lr To 2 Step -1  'Start at bottom and work up
        Set fn = sh1.Range("A:A").Find(sh2.Cells(i, 1).Value, , xlValues, xlWhole)
        If Not fn Is Nothing Then
            fn.EntireRow.Copy
            sh2.Rows(i + 1).Insert
        End If
    Next
    Application.CutCopyMode = False
End Sub

 

[masterelaichi]

Thanks but I have no clue about macros or VBA coding. Is there another way to do this?

 

[JLGWhiz]

Thanks but I have no clue about macros or VBA coding. Is there another way to do this?

Could be, but I am not aware of it.

 

[masterelaichi]

Is there a way to do this in power query or power pivot perhaps?

 

[mikerickson]

To do this without macros

Put a helper column on Sheet 1 with the formula =ROW(A1)+.2
Put a helper columns on Sheet2 with the formula =ROW(A1)+.1

Copy/PasteSpecial Values those helper columns to turn the formulas into values

So on sheet 1 you should have two columns

1A 1.2
1B 2.2
1C 3.2
..
And on the second
2A 1.1
2B 2.1
...


Then copy both columns from sheet 1. Paste that at the bottom of sheet2, giving

2A 1.1
2B 2.1
2C 3.1
2D 4.1
2E 5.1
2F 6.1
1A 1.2
1B 2.2
1C 3.2
1D 4.2

Then sort those two columns on the values in the helper column.