Automate insertion of row data

[MrJJP]

Hello, would like to automatically add certain row data when data in other cells is added. In the below screenshot I have unchanging data in columns B & C, with the expectation that users will input data into the respective columns D, E & F. So my starting point in the example is B2:F5, and I would like it to come out to looking like the example in B8:F13. Another way to say this, is in B8:F13, if a user input the data into D8, E8, and/or F8, upon hitting enter, it would create a new row underneath it with blanks (ready for the next user's input) into D9:F9, but still copy/retain the original data values from B8 and C8. I'd prefer to do it without VBA, but can muster through that with any additional help (my total number of rows will be around 35 and I may have 3-4 additional columns). Thanks for any help!

 

[myall_blues]

You can't do it without VBA because it's not possible to change a worksheet structure with formulas (i.e. insert rows etc). So to that end:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cRow As Long
    If Intersect(Target, Range("D3:F40")) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    cRow = Target.Row
    Range("B" & cRow & ":C" & cRow).Copy
    Range("B" & cRow + 1).Insert Shift:=xlDown
    Application.CutCopyMode = False
    Application.EnableEvents = True
End Sub

This code needs to go in the Worksheet_Change event of your worksheet. To do that open the VBA editor (ALT+F11) and on the left hand side right-click on the sheet you're working in and click View Code. Then paste the code in the window that appears.

 

[MrJJP]

Thanks for the assistance, very much appreciated! It's working in part but there is an issue though I think I figured out solution. So the issue that I was facing is when I place my cursor in the "Date" column (D), enter a date, and then press tab to enter data into the "Participants" column, it would add a row, and then when I would tab to the "POC" (F) column, it would add yet another row. What I did, and please tell me if this will work (it did on my test run, but want to be wary of any potential issues), is I changed the 3rd line of the code for the "(Target, Range("F3:F40")..." so only when a value in column F is added, will it add a duplicate row.
Also, I added a few extra columns in my final sheet to be copied, so instead of just columns B and C, it's B, C, D. I think I would just change the 6th line of code from "C" to "D"? Thanks again!!

 

[myall_blues]

Yes correct on both counts.

 

[MrJJP]

Great, thanks for your help myall_blues!!

 

[myall_blues]

You’re welcome. Glad it worked for you.