Conditionally insert a duplicate row Solved/Closed

Question

I have a table with hundreds of rows but here's a basic example:
__a___b_____c____
1 Color Dates  Completed (headings)
2 red  Date1  No
3 red  Date2  Yes
4 red  Date2  No
5 blue Date3  Yes (cell c5 is active)

When I'm filling out a new row (let's say row 5) and put "Yes" in column C, I need a formula/macro that will create a duplicate row underneath the active row. If it's "No" or blank then do nothing. The duplicate row should copy everything exept in column c.  The macro should only affect the active row, not all the rows in the table with "Yes". If it's possible, I would like it to have a shortcut key of "ctrl+y".
It should look like this:
__a___b_____c___
1 Color Dates  Completed (headings)
2 red  Date1  No
3 red  Date2  Yes
4 red  Date2  No
5 blue Date3  Yes 
6 blue Date3

Please help! Greatly appreciated :)

TrowaD · Answer

Hi Acohen,

A7: =IF(C6="Yes",A6,"")
B7: =IF(C6="Yes",B6,"")

Drag these two formula's down.

If C6 is "No" you can just overwrite the formula's.

Of course a macro can be written for you if you desire, but the formula's should suffice.

Best regards,
Trowa

acohen12 · Answer

Thanks TrowaD, but I apologize because I should have noted that there will be rows beyond 6 and 7 with data already filled in, so I would not be able to drag down.  
I would like it to insert a new row automatically if I put "Yes" in column C, as well as copy and past column A and B into the new row.

Is that too complicated?
I really appreciate the help.

TrowaD · Answer

Not at all Acohen, I'm just trying to look for the easiest solution.

Here is the code:
Sub test()

If ActiveCell.Value = "Yes" Then
    ActiveCell.Offset(1, 0).EntireRow.Insert
    Range(ActiveCell.Offset(0, -2), ActiveCell.Offset(0, -1)).Copy ActiveCell.Offset(1, -2)
End If

End Sub
Run the code when you have selected a single cell in column C.

To create a shortcut, goto top menu > Extra > Macro > Macro's (or Alt+F8).
In the small window that pops up goto options and enter your short-cut combi.

Best regards,
Trowa

acohen12 · Answer

Very nice.  Is there a line of code that can be input that will automatically run the macro as soon as Yes entered, instead of using a shortcut key each time?

TrowaD · Answer

Hi Acohen,

To do as you requested paste the following code in the big white field after selecting "View code" by right-clicking the sheets tab:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("C2:C100")) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub

If Target.Value = "Yes" Then
    Target.Offset(1, 0).EntireRow.Insert
    Range(Target.Offset(0, -2), Target.Offset(0, -1)).Copy Target.Offset(1, -2)
End If

End Sub
Best regards,
Trowa

Conditionally insert a duplicate row

5 responses

Similar discussions

Subscribe To Our Newsletter!