Conditionally insert a duplicate row

Solved/Closed
Report
Posts
5
Registration date
Thursday June 6, 2013
Status
Member
Last seen
June 13, 2013
-
Posts
2847
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 13, 2022
-
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 :)

5 replies

Posts
2847
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 13, 2022
491
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
1
Posts
5
Registration date
Thursday June 6, 2013
Status
Member
Last seen
June 13, 2013

Why I do it, an error comes up: Insert method of Range class failed
0
Posts
2847
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 13, 2022
491
Hi Acohen,

The way I can recreate the error is by protecting the sheet (except column C), thus prohibiting rows to be inserted.

So please check if rows can be inserted.

Otherwise post your file, for closer inspection, using a filesharing site like:
www.speedyshare.com
or
ge.tt
Be carefull with personal info.

Best regards,
Trowa
0
Posts
2847
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 13, 2022
491
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
0
Posts
5
Registration date
Thursday June 6, 2013
Status
Member
Last seen
June 13, 2013

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.
0
Posts
2847
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 13, 2022
491
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
0
Posts
5
Registration date
Thursday June 6, 2013
Status
Member
Last seen
June 13, 2013

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?
0