VBA code for macro button not working to transfer row data to another worksheet

[lukaderanged]

I'm trying to transfer a row's data to another worksheet based on the cell value. Here is the info:
The worksheets are named Drafts and Final Work. I want the data to transfer from Drafts to Final Work without copying over any existing information in the Final Work worksheet.
Row 1 is the headers and data starts on row 2.
Column Y contains the cell value I want to use to transfer data. It's a dropdown with the only option of "Final".

The code appears to be working in removing the row from the Drafts worksheet but it does not copy over to the Final Work worksheet. I had protections but took them all off in case these were preventing the transferring of information, it still doesn't work.

Here is the code. Any idea why it might not work? This code was working on a similar workbook I was using previously but that workbook was compromised and had to recreate.I'm pretty sure I'm missing something but I can't figure it out. The other workbook also had code for temporarily pausing Application.CellDragAndDrop = False & Application.CutCopyMode = False but I can't remember that part of the code, didn't copy it somewhere, and I'm not able to access the old workbook. Could that part have the missing code? Thanks in advance to anyone that can help as I'm not very knowledgeable on Excel.

Private Sub CommandButton1_Click()
    Dim xRg As Range
    Dim xCell As Range
    Dim A As Long
    Dim B As Long
    Dim C As Long
    A = Worksheets("Drafts").UsedRange.Rows.Count
    B = Worksheets("Final Work").UsedRange.Rows.Count
    If B = 1 Then
       If Application.WorksheetFunction.CountA(Worksheets("Final Work").UsedRange) = 0 Then B = 0
    End If
    Set xRg = Worksheets("Drafts").Range("Y2:Y" & A)
    On Error Resume Next
    Application.ScreenUpdating = False
    For C = 1 To xRg.Count
        If CStr(xRg(C).Value) = "Final" Then
            xRg(C).EntireRow.Copy Destination:=Worksheets("Final Work").Range("A" & B + 1)
            xRg(C).EntireRow.Delete
            If CStr(xRg(C).Value) = "Final" Then
                C = C - 1
            End If
            B = B + 1
        End If
    Next
    Application.ScreenUpdating = True
End Sub

 

[rjwalker1973]

I'll keep looking but I think the find location and paste goes something like this


lastrow = Sheets("Main").Cells(Rows.Count, "A").End(xlUp).Row
ActiveSheet.Paste Destination:=Worksheets("Main").Range("A" & lastrow)

Heck I think I actually got this piece of code from this forum. I'll try and see if I can find that post . It would probably help more than my snippet of code will.