Microsoft Office Forums - View Single Post - Advice on macro that moves rows across sheets

NoSparks · 12-13-2017, 08:46 PM

I have to admit I don't understand what's going on with that table on the "Open Tasks" sheet.
It's supposed to behave the same as the table on the "Triage" sheet, but for some reason it won't expand.

Try this for the change event

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim nxtRow As ListRow

' Limit to a single cell change in column 5
If Target.Count > 1 Or Target.Column <> 5 Then Exit Sub
    
If UCase(Target.Value) = "YES" Then
    Application.EnableEvents = False
    Set nxtRow = Sheets("Open Tasks").ListObjects("Table2").ListRows.Add
    Intersect(Target.EntireRow, UsedRange).Copy
    nxtRow.Range.PasteSpecial xlPasteValues
    Target.EntireRow.Delete
    Application.EnableEvents = True
    Exit Sub
End If

End Sub