Conditionally insert a duplicate row

Solved/Closed
acohen12 Posts 5 Registration date Thursday June 6, 2013 Status Member Last seen June 13, 2013 - Jun 6, 2013 at 02:57 PM
TrowaD Posts 2901 Registration date Sunday September 12, 2010 Status Moderator Last seen October 4, 2022 - Jun 17, 2013 at 10:32 AM
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

TrowaD Posts 2901 Registration date Sunday September 12, 2010 Status Moderator Last seen October 4, 2022 525
Jun 13, 2013 at 10:37 AM
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
acohen12 Posts 5 Registration date Thursday June 6, 2013 Status Member Last seen June 13, 2013
Jun 13, 2013 at 06:45 PM
Why I do it, an error comes up: Insert method of Range class failed
0
TrowaD Posts 2901 Registration date Sunday September 12, 2010 Status Moderator Last seen October 4, 2022 525
Jun 17, 2013 at 10:32 AM
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
TrowaD Posts 2901 Registration date Sunday September 12, 2010 Status Moderator Last seen October 4, 2022 525
Jun 10, 2013 at 10:33 AM
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
acohen12 Posts 5 Registration date Thursday June 6, 2013 Status Member Last seen June 13, 2013
Jun 10, 2013 at 03:59 PM
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
TrowaD Posts 2901 Registration date Sunday September 12, 2010 Status Moderator Last seen October 4, 2022 525
Jun 11, 2013 at 10:19 AM
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

Didn't find the answer you are looking for?

Ask a question
acohen12 Posts 5 Registration date Thursday June 6, 2013 Status Member Last seen June 13, 2013
Jun 11, 2013 at 06:04 PM
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