Code for inserting a row automatically based on a value in a particular cell

[keithjones33]

Hi all.

I have a spread sheet which is completed using production data. my issue is that when a machine runs more than 1 product only the info. for the one product is collected. What I want to do is enter a figure in column "E", its either going to be 1 or 2. If "2" is entered then I want to insert a new row below the row data is being entered into. Ideally it would also be good to copy the in info. from the cells in columns "A", "B" and "C" into the new row. I know this would have to be done using VBA coding but I have no experience of coding.

Thanks
​

 

[DanteAmor]

I didn't quite understand where the row is going to be inserted, but let's start with this:
Put the following code in the events of the sheet:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("E:E")) Is Nothing Then
    If Target.Count > 1 Then Exit Sub
    If Target.Value = 2 Then
      Target.Offset(1).EntireRow.Insert
      Range("A" & Target.Row & ":C" & Target.Row).Copy Range("A" & Target.Row + 1)
    End If
  End If
End Sub

SHEET EVENT
Right click the tab of the sheet you want this to work, select view code and paste the code into the window that opens up.


Now type a 2 in cell E4, in automatic the sheet inserts a row below row 4 and copies cells A4 to C4 in A5 to C5.
I hope it helps you.

 

[My Aswer Is This]

Try this:

I assuming if you enter a 1 you want 1 row inserted
If you enter a 2 you want 2 rows inserted.
Was not sure of this.

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window
When you enter the value 1 or 2 in column E the script will run.

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  8/9/2019  11:42:53 AM  EDT
If Target.Column = 5 Then
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
With Rows(Target.Row).Offset(1).Resize(Target.Value)
    Select Case Target.Value
        Case 1
            .Resize(Target.Value).Insert
            Cells(Target.Row, 1).Resize(, 3).Copy Cells(Target.Row + 1, 1).Resize(Target.Value)
        Case 2
            .Resize(Target.Value).Insert
            Cells(Target.Row, 1).Resize(, 3).Copy Cells(Target.Row + 1, 1).Resize(Target.Value)
    End Select
End With
End If
End Sub

 

[keithjones33]

Thanks DanteAmor that works perfect for what I wanted. Having tried it it now throws up another issue. The sheet the info. is entered into is a template sheet, the sheet is saved when the production info. has been entered then copied into another folder and renamed with the date and whether its "AM or PM". The template is then cleared ready for the next shift info. to be entered at this point the sheet needs to return to its original (1 line per machine).

I hope this makes sense.

Keith

 

[keithjones33]

My answer, thanks.
In answer to your question I only want 1 line entered directly below the line where "2" is entered. As you see from my reply to "DanteAmor" it now throws up another issue.

Keith

 

[DanteAmor]

Thanks DanteAmor that works perfect for what I wanted. Having tried it it now throws up another issue. The sheet the info. is entered into is a template sheet, the sheet is saved when the production info. has been entered then copied into another folder and renamed with the date and whether its "AM or PM". The template is then cleared ready for the next shift info. to be entered at this point the sheet needs to return to its original (1 line per machine).

I hope this makes sense.

Keith

I don't know what you mean by:

at this point the sheet needs to return to its original (1 line per machine)

That was not in your original request, so you should explain more clearly what you need.

 

[keithjones33]

Hi. Sorry it wasn't in my original request as I hadn't anticipated this issue. What I mean is if I enter "2" into column "E" it creates a second line which is exactly what you code does and what I want to happen. But what happens is once the production data is finished being entered the excel file is saved. This file is then copied into a folder and then renamed. The original file is then used again to enter the collected production data from the next shift. The issue is that when I reopen the file to enter further data I have two lines for the same machine due to the info I had previously entered. Therefore when I reopen the file it needs to only have one line per machine as previously.

I hope this explains it better.

Keith

 

[DanteAmor]

If I understand correctly, you must save the file with another name and keep the "template" unchanged, so that when you open the "template" it is found in an original way. If you want to work with the new file, then you open the second file.

 

[My Aswer Is This]

Normally when using a Template. We never modify the Template after setting it up the way we want.

If our code modifies the template we always use SaveAs and give the newly created sheet a new name.
Then when closing the Template we do not save the changes. We normally would not go back in and try to reset the Template back to it's original condition.

 

[keithjones33]

Thanks for the update. I will use the method mentioned.

Keith