Moving rows from one sheet/table to multiple sheets/tables

[mybossmademecomehere]

I am looking for a VBA loop that can move rows from the unsorted table to multiple tables labeled based on staff names without overriding or clearing off formatting as I have a bit of data validation and conditional formatting on the unsorted and staff assignment pages that I need to keep so I am getting consistent responses from staff. So probably just a simple copy paste then delete would do. I did put in some code I found that prevents using CUT so that it doesn't take out data validation. The staff assignments will be entered in column L and all of the names for the sheets match the name for the tables on the respective pages. I do have a labeled list that has all of the staff names if that would make the code/process any easier. That is on my values sheet with the table names assignments. I would have this being implemented by adding one of the buttons for a staff member to click on so that it would move the rows to the different tables once all of the rows have all been assigned.

I know there is a lot of similar requests that have been put out, but my VBA really only gets me as far as being able to update and use what is clearly labeled with notes and doing minor changes. Anything I have tried on this and other sites I get errors that I cannot work myself through. I appreciate any help I can get. I have been trying to work through this for a lot longer than I'd like to admit.

 

[mybossmademecomehere]

I was able to find something I could figure out that does most of what I need. Because of the rows moving it overrides the data validation in the staff sheets and I need to only move columns A-L. Also for some reason it is not adding additional rows to the tables, it is only adding the first one to the table and then pasting the remainder outside of the table range. Any help with figuring this out would be greatly appreciated!

The code is:

Sub MoveAssign()
Dim sourceSheet As Worksheet
Dim targetSheet As Worksheet
Dim lastRow As Long
Dim i As Long
Dim assignment As String

' Set the source and target sheets
Set sourceSheet = ThisWorkbook.Worksheets("unsorted")

' Find the last row in the source sheet
lastRow = sourceSheet.Cells(sourceSheet.Rows.Count, "L").End(xlUp).Row

' Loop through each row in the source sheet
For i = 2 To lastRow
' Check if cell in column L contains "StaffOne"
If sourceSheet.Cells(i, "L").Value = "StaffOne" Then
Set targetSheet = ThisWorkbook.Worksheets("StaffOne")
' Copy the entire row to the target sheet
sourceSheet.Rows(i).Copy Destination:=targetSheet.Cells(targetSheet.Rows.Count, "A").End(xlUp).Offset(1)
' Delete the row from the source sheet
sourceSheet.Rows(i).Delete
' Decrement the loop counter as the rows are shifting up
i = i - 1
' Update the last row value
lastRow = lastRow - 1
End If
Next i

End Sub