Tables: Cut & Paste Automatically - Value driven code

[ktobiano]

Quote requests come in and I record them in a table.

Based on the "status" cell, I need specific rows to automatically move to the appropriate table on a different sheet for another department to work on.

I looked up some code to move an entire row to the correct sheet based on the status cell value, but it pasted the data BELOW the actual table instead of inserting a new table row and then pasting the data.

To keep it simple here are 4 of my columns
Date, Name, ID, Status

Here are my sheet/table names:
Quote, FollowUp, Awarded, Lost

This spreadsheet can get very large very quick so I'd prefer a simple code that didn't eat up resources.

Any help is GREATLY appreciated. I am not great at VBA - know just enough to be dangerous.

 

[NoSparks]

Maybe try playing around with something along the lines of this

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim TblQ As ListObject, DestTbl As ListObject
    Dim LstRow As Long
    Dim NewRow As ListRow

Set TblQ = ListObjects("Quote")

'limit to single cell
If Target.Count > 1 Then Exit Sub
'limit to 4th column
If Intersect(Target, TblQ.ListColumns(4).DataBodyRange) Is Nothing Then
    Exit Sub
Else
    'the table row to move
    LstRow = TblQ.ListRows(Target.Row - TblQ.HeaderRowRange.Row).Index
    'where to move it
    If UCase(Target) = "F" Then Set DestTbl = Sheets("FollowUP").ListObjects("FollowUP")
    If UCase(Target) = "A" Then Set DestTbl = Sheets("Awarded").ListObjects("Awarded")
    If UCase(Target) = "L" Then Set DestTbl = Sheets("Lost").ListObjects("Lost")
   'insert table row
    Set NewRow = DestTbl.ListRows.Add(AlwaysInsert:=True)
    'populate it
    NewRow.Range.Cells(1, 1).Resize(, 4).Value = TblQ.ListRows(LstRow).Range.Value
    'remove original
    Application.EnableEvents = False
    TblQ.ListRows(LstRow).Delete
    Application.EnableEvents = True
End If
End Sub

A couple of references
The VBA Guide To ListObject Excel Tables — The Spreadsheet Guru
Excel: Working with Tables (VBA)

Good luck

 

[ktobiano]

Thank you so much! I'll play with it on Monday!!