VBA help - automatically move row to the bottom of a table once a specific value is entered.

[emztruong]

Hello!

Can someone please kindly assist with the following?

Once cell F is marked with a "y", I'd like that specific row to move to the bottom of the table. Ideally I want to keep a record of all previous data so the table would need to keep expanding but not sure if that's a possibility.

Thanks in advance
Emily

 

[Yongle]

Welcome to MrExcel

Place code in SHEET code window
- NOT in a module, NOT in ThisWorkbook code window

Right click on sheet tab , select View Code , paste code into that window

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim tbl As ListObject, wRow As Long
    Set tbl = Me.ListObjects(1)
    If Target.CountLarge > 1 Then Exit Sub
        If Not Intersect(Target, tbl.ListColumns("Paid Y/N").Range) Is Nothing Then
            If UCase(Target.Value) = "Y" Then
            Application.ScreenUpdating = False
            wRow = Target.Row - tbl.HeaderRowRange.Row
            tbl.ListRows.Add AlwaysInsert:=True
            tbl.DataBodyRange.Rows(wRow).Copy tbl.DataBodyRange.Rows(tbl.ListRows.Count)
            tbl.ListRows(wRow).Delete
        End If
    End If
End Sub

 

[emztruong]

Hi Yongle,

That didn't seem to work. Also, it needs to be "Y" for column F "Collected".

Thanks
Emily

 

[Yongle]

The code works for me - so we should be able to make it work for you
Please post the amended code that you used and In will test it