Excel Macro to insert two rows based on condition and copy and paste multiple cells

[mannabhalo]

Hi I need help with inserting two rows based on a condition, and then copy and paste cells into the new rows. I have attached an excel workbook that has what my sheet looks like now and what I want it to look like. Please help.

Thanks,
John. To Test.xlsx

[JieJenn]

Please Login or Register  to view this content.

[mannabhalo]

Hi JieJenn,
Thanks a lot for your prompt reply.
Unfortunately the macro is not being added to the new rows created for all of the lines. In line # 8 from "should look like" Tab the columns should be added to the newly created rows and not replace the values already there. Please help me.
Thanks,
John.

[mannabhalo]

Hi, is there any other way to solve this problem? Please help!!!

[mannabhalo]

Hi I want to insert 2 rows and in each new row copy and paste from rows above. Please help, my sheet attached in the first post.

Thanks,
John.

[mannabhalo]

Copy of To Test (3).xlsmHi, I have been playing around with the code but not getting the results I want. In the new line I have added a "~". Now based on that I want the code to go back to "YES" and copy and paste the lines to the line beginning with "~". This is what I have so far.

Sub Test()
Dim LR(1) As Long, i As Long, j As Long

With ActiveSheet
LR(0) = .Cells(Rows.Count, 1).End(xlUp).Row

For i = LR(0) To 3 Step -1
If UCase(.Cells(i, 1)) = "YES" Then
.Rows(i & ":" & i + 1).Insert
ActiveSheet.Cells(i, 1).Value = "~"
End If
LR(1) = .Cells(Rows.Count, 1).End(xlUp).Row
For j = 2 To LR(1)
If UCase(.Cells(j, 1)) = "~" Then
.Cells(i, "I").Value = .Cells(j, "J")
.Cells(i + 1, "I").Value = .Cells(j, "K")
.Cells(i, "M").Value = .Cells(j, "N")
.Cells(i + 1, "M").Value = .Cells(j, "O")
End If
Next j

Next i

End With

Please Help!!!

[mannabhalo]

Does anyone know how to solve this? Thanks!