VBA to Automatically insert row based on cell text in current row (with partial data)

[pstreet]

Hi Guys, Sorry if this is an easy question, I'm a VBA beginner. This is a training tracker used within our team. Is there a module that;

automatically detects cell value "Did Not Attend" in column "J" and if TRUE - inserts a duplicated row below (without the cell data of "J" & "L:S")

Many Thanks in Advance!

SO THIS....
[TABLE="width: 1849"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[TD]P[/TD]
[TD]Q[/TD]
[TD]R[/TD]
[TD]S[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Employee ID[/TD]
[TD] [/TD]
[TD]Name[/TD]
[TD]Job Title[/TD]
[TD]Location[/TD]
[TD]Budget Month[/TD]
[TD]Actual Month[/TD]
[TD]Expiry Date[/TD]
[TD]Qualification[/TD]
[TD]Trainer[/TD]
[TD]Start Date[/TD]
[TD]Finish Date if different[/TD]
[TD]Month[/TD]
[TD]Start Time[/TD]
[TD]Finish Time[/TD]
[TD]Venue[/TD]
[/TR]
[TR]
[TD]2017 Budget[/TD]
[TD]Essential[/TD]
[TD]Initial[/TD]
[TD]DAV[/TD]
[TD] [/TD]
[TD]Gavin Davies[/TD]
[TD]Operative[/TD]
[TD]NYC[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Asbestos Awareness[/TD]
[TD]Pearson Vue[/TD]
[TD="align: right"]1[/TD]
[TD] [/TD]
[TD]May[/TD]
[TD]08:03[/TD]
[TD]09:03[/TD]
[TD]Bolty[/TD]
[/TR]
[TR]
[TD]2017 Budget[/TD]
[TD]Essential[/TD]
[TD]Renewal[/TD]
[TD]DAV[/TD]
[TD] [/TD]
[TD]Gavin D[/TD]
[TD]Operative[/TD]
[TD]NYC[/TD]
[TD] [/TD]
[TD]Did not attend[/TD]
[TD="align: right"]07/09/2017[/TD]
[TD]National Water Hygiene Scheme[/TD]
[TD]Pearson Vue[/TD]
[TD="align: right"]2[/TD]
[TD] [/TD]
[TD]June[/TD]
[TD]08:02[/TD]
[TD]09:02[/TD]
[TD]Bolty[/TD]
[/TR]
[TR]
[TD]2017 Budget[/TD]
[TD]Essential[/TD]
[TD]Renewal[/TD]
[TD]DAV[/TD]
[TD] [/TD]
[TD]Gavin D[/TD]
[TD]Operative[/TD]
[TD]NYC[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]07/09/2017[/TD]
[TD]National Water Hygiene Scheme[/TD]
[TD]Pearson Vue[/TD]
[TD="align: right"]3[/TD]
[TD] [/TD]
[TD]July[/TD]
[TD]08:01[/TD]
[TD]09:01[/TD]
[TD]Bolty[/TD]
[/TR]
[TR]
[TD]2017 Budget[/TD]
[TD]Essential[/TD]
[TD]Initial[/TD]
[TD]DAV[/TD]
[TD] [/TD]
[TD]Gavin D[/TD]
[TD]Operative[/TD]
[TD]NYC[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Working at Heights[/TD]
[TD]Pearson Vue[/TD]
[TD="align: right"]4[/TD]
[TD] [/TD]
[TD]August[/TD]
[TD]08:00[/TD]
[TD]09:00[/TD]
[TD]Bolty[/TD]
[/TR]
[TR]
[TD]2017 Budget[/TD]
[TD]Essential[/TD]
[TD]Initial[/TD]
[TD]DAV[/TD]
[TD] [/TD]
[TD]Gavin D[/TD]
[TD]Operative[/TD]
[TD]NYC[/TD]
[TD] [/TD]
[TD]March[/TD]
[TD] [/TD]
[TD]Confined Space Entry - City & Guilds[/TD]
[TD]Ronnie Traiing[/TD]
[TD="align: right"]9[/TD]
[TD] [/TD]
[TD]July[/TD]
[TD]08:28[/TD]
[TD]17:02[/TD]
[TD]Skellon[/TD]
[/TR]
[TR]
[TD]Health and Safety Test[/TD]
[TD]Essential[/TD]
[TD]Initial[/TD]
[TD]M_MUL[/TD]
[TD] [/TD]
[TD]Gavin Mui[/TD]
[TD]Estimator[/TD]
[TD]WA[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Health and Safety test[/TD]
[TD]Ronnie Traiing[/TD]
[TD="align: right"]10[/TD]
[TD] [/TD]
[TD]August[/TD]
[TD]08:29[/TD]
[TD]17:01[/TD]
[TD]Skellon[/TD]
[/TR]
[TR]
[TD]2017 Budget[/TD]
[TD]Essential[/TD]
[TD]Initial[/TD]
[TD]M_MUL[/TD]
[TD] [/TD]
[TD]Gavin Mui[/TD]
[TD]Estimator[/TD]
[TD]WA[/TD]
[TD] [/TD]
[TD]June[/TD]
[TD] [/TD]
[TD]Temporary Works Coordinator[/TD]
[TD]Ronnie Traiing[/TD]
[TD="align: right"]11[/TD]
[TD] [/TD]
[TD]September[/TD]
[TD]08:30[/TD]
[TD]17:00[/TD]
[TD]Skellon[/TD]
[/TR]
[TR]
[TD]2017 Budget[/TD]
[TD]Essential[/TD]
[TD]Initial[/TD]
[TD]M_MUL[/TD]
[TD] [/TD]
[TD]Gavin Mui[/TD]
[TD]Estimator[/TD]
[TD]WA[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]CANDY[/TD]
[TD]Pearson Vue[/TD]
[TD="align: right"]2[/TD]
[TD] [/TD]
[TD]June[/TD]
[TD]08:02[/TD]
[TD]09:02[/TD]
[TD]Bolty[/TD]
[/TR]
[TR]
[TD]2017 Budget[/TD]
[TD]Essential[/TD]
[TD]Initial[/TD]
[TD]M_MUL[/TD]
[TD] [/TD]
[TD]Gavin Mui[/TD]
[TD]Estimator[/TD]
[TD]WA[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]CSCS card[/TD]
[TD]Pearson Vue[/TD]
[TD="align: right"]3[/TD]
[TD] [/TD]
[TD]July[/TD]
[TD]08:01[/TD]
[TD]09:01[/TD]
[TD]Bolty[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BECOMES...[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Employee ID[/TD]
[TD] [/TD]
[TD]Name[/TD]
[TD]Job Title[/TD]
[TD]Location[/TD]
[TD]Budget Month[/TD]
[TD]Actual Month[/TD]
[TD]Expiry Date[/TD]
[TD]Qualification[/TD]
[TD]Trainer[/TD]
[TD]Start Date[/TD]
[TD]Finish Date if different[/TD]
[TD]Month[/TD]
[TD]Start Time[/TD]
[TD]Finish Time[/TD]
[TD]Venue[/TD]
[/TR]
[TR]
[TD]2017 Budget[/TD]
[TD]Essential[/TD]
[TD]Initial[/TD]
[TD]DAV[/TD]
[TD] [/TD]
[TD]Gavin Davies[/TD]
[TD]Operative[/TD]
[TD]NYC[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Asbestos Awareness[/TD]
[TD]Pearson Vue[/TD]
[TD="align: right"]1[/TD]
[TD] [/TD]
[TD]May[/TD]
[TD]08:03[/TD]
[TD]09:03[/TD]
[TD]Bolty[/TD]
[/TR]
[TR]
[TD]2017 Budget[/TD]
[TD]Essential[/TD]
[TD]Renewal[/TD]
[TD]DAV[/TD]
[TD] [/TD]
[TD]Gavin D[/TD]
[TD]Operative[/TD]
[TD]NYC[/TD]
[TD] [/TD]
[TD]Did not attend[/TD]
[TD="align: right"]07/09/2017[/TD]
[TD]National Water Hygiene Scheme[/TD]
[TD]Pearson Vue[/TD]
[TD="align: right"]2[/TD]
[TD] [/TD]
[TD]June[/TD]
[TD]08:02[/TD]
[TD]09:02[/TD]
[TD]Bolty[/TD]
[/TR]
[TR]
[TD]2017 Budget[/TD]
[TD]Essential[/TD]
[TD]Renewal[/TD]
[TD]DAV[/TD]
[TD][/TD]
[TD]Gavin D[/TD]
[TD]Operative[/TD]
[TD]NYC[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]07/09/2017[/TD]
[TD]National Water Hygiene Scheme[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]2017 Budget[/TD]
[TD]Essential[/TD]
[TD]Renewal[/TD]
[TD]DAV[/TD]
[TD] [/TD]
[TD]Gavin D[/TD]
[TD]Operative[/TD]
[TD]NYC[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]07/09/2017[/TD]
[TD]National Water Hygiene Scheme[/TD]
[TD]Pearson Vue[/TD]
[TD="align: right"]4[/TD]
[TD] [/TD]
[TD]August[/TD]
[TD]08:00[/TD]
[TD]09:00[/TD]
[TD]Bolty[/TD]
[/TR]
[TR]
[TD]2017 Budget[/TD]
[TD]Essential[/TD]
[TD]Initial[/TD]
[TD]DAV[/TD]
[TD] [/TD]
[TD]Gavin D[/TD]
[TD]Operative[/TD]
[TD]NYC[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Working at Heights[/TD]
[TD]Ronnie Traiing[/TD]
[TD="align: right"]9[/TD]
[TD] [/TD]
[TD]July[/TD]
[TD]08:28[/TD]
[TD]17:02[/TD]
[TD]Skellon[/TD]
[/TR]
[TR]
[TD]2017 Budget[/TD]
[TD]Essential[/TD]
[TD]Initial[/TD]
[TD]DAV[/TD]
[TD] [/TD]
[TD]Gavin D[/TD]
[TD]Operative[/TD]
[TD]NYC[/TD]
[TD] [/TD]
[TD]March[/TD]
[TD] [/TD]
[TD]Confined Space Entry - City & Guilds[/TD]
[TD]Ronnie Traiing[/TD]
[TD="align: right"]10[/TD]
[TD] [/TD]
[TD]August[/TD]
[TD]08:29[/TD]
[TD]17:01[/TD]
[TD]Skellon[/TD]
[/TR]
[TR]
[TD]Health and Safety Test[/TD]
[TD]Essential[/TD]
[TD]Initial[/TD]
[TD]M_MUL[/TD]
[TD] [/TD]
[TD]Gavin Mui[/TD]
[TD]Estimator[/TD]
[TD]WA[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Health and Safety test[/TD]
[TD]Ronnie Traiing[/TD]
[TD="align: right"]11[/TD]
[TD] [/TD]
[TD]September[/TD]
[TD]08:30[/TD]
[TD]17:00[/TD]
[TD]Skellon


[/TD]
[/TR]
</tbody><colgroup><col><col span="2"><col><col><col><col><col><col><col><col><col><col><col span="4"><col><col></colgroup>[/TABLE]

 

[Fluff]

Hi & welcome to the board.
Is this what you want?

Sub InsertNewRow()

   Dim Fnd As Range
   Dim Qty As Long
   Dim Cnt As Long
   
   Set Fnd = Range("J1")
   With Columns(10)
      Qty = WorksheetFunction.CountIf(Columns(10), "Did not attend")
      For Cnt = 1 To Qty
         Set Fnd = .Find("Did not attend", Fnd, , xlWhole, , , False, , False)
         Fnd.Offset(1).EntireRow.Insert
         Fnd.Resize(2).EntireRow.FillDown
         Fnd.Offset(1).ClearContents
         Fnd.Offset(1, 3).Resize(, 7).ClearContents
      Next Cnt
   End With
End Sub

 

[pstreet]

Sorry this doesn't seem to work automatically in my full worksheet.

Could I combine it with the following VBA code, I would like it to insert a duplicate row below (only data in columns 1,2,3,4,5 & 6) within my worksheet.

Private Sub Worksheet_Change1(ByVal Target As Range)
'Determine if change was made to a single cell in Column E
 If Target.Column = 5 And Target.Cells.Count = 1 Then
'Determine if Termed was chosen
  If Target = "Did not attend" Then
'If Yes...
''Disable Events
    Application.EnableEvents = False
'' Insert a row below
     ActiveCell.Offset(1).EntireRow.Insert
''Copy, Paste
      Rows(Target.Row).EntireRow.Copy _
       Destination:=Sheets("Non Attendance").Range("A" & nxtRw)
''Re-enable Events
    Application.EnableEvents = True
   
  End If
  End If
End Sub

Thanks so much!

 

[Fluff]

In you op you said col J has "Did not attend" & to copy cols 1-9 & 11
Now the code you posted is col E & you say only copy cols 1-6

Which is correct?

 

[pstreet]

The worksheet was modified, (but now locked and won't change) so col E & cols 1-6 are required

 

[Fluff]

Untested but how about

Private Sub Worksheet_Change(ByVal Target As Range)
'Determine if change was made to a single cell in Column E
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Column = 5 Then
      'Determine if Termed was chosen
      If Target = "Did not attend" Then
      'If Yes...
      ''Disable Events
      Application.EnableEvents = False
      '' Insert a row below
      Target.Offset(1).EntireRow.Insert
      ''Copy, Paste
      Target.Offset(, -4).Resize(, 6).Copy Sheets("Non Attendance").Range("A" & Rows.Count).End(xlUp).Offset(1)
      ''Re-enable Events
      Application.EnableEvents = True
      
      End If
   End If
End Sub

 

[pstreet]

This works a charm! thanks so much!

 

[Fluff]

Glad to help & thanks for the feedback