UserForm to Search for, Cut, and Paste to another sheet

[Glen M]

I have searched and searched but cannot find the answer on how to do this. ALL THIS IS DONE FROM MY USERFORM (see pic). First my user will search for what they want (can use any of the purple buttons). The code returns the results to the listbox. Next the user will select the right PO from the listbox and the information populates the textboxes. All I need code for is, when the user clicks the Red reconcile button, the PO that is selected will be cut and the values are pasted to another sheet and then the row is deleted. Some other information you might find useful. My source data resides in a table called T-Inv on a sheet called Purchase Orders. There is a header row at A14, the data starts at A15:Y15 . The destination table is called Table5 on a sheet called Reconciled. There is a header row in A1 and the data can start to be pasted at A2. I have looked at a lot of different codes and questions and none of them seem to quite do what I want.

Private Sub CmdRecon_Click()

   Dim i As Long
    For i = Cells(Rows.Count, 25).End(xlUp).Row To 1 Step -1
        If Cells(i, 25) = "y" Then
            Range("a" & i & ":Y" & i).Copy
Sheet3.Cells(Rows.Count, 1).End(xlUp).Offset(1).PasteSpecial xlPasteValues
            Cells(i, 25).EntireRow.Delete
        End If
    Next


End Sub

This is working but it is looking for a "Y" in column 25. I want to eliminate this step for the user. Currently the user puts a "Y" in the reconciled box (see pic) then hits update then hits reconcile. I want the user to just be able to click reconcile and be done.

 

[Glen M]

Clarification: I want to click the reconcile button and cut and paste the row that has the txtPO.value (from the userform) onto the other sheet.

 

[NoSparks]

Perhaps something along the lines of this.
Had to change the source table name to T_Inv as T-Inv was not accepted as a valid table name in my Excel 2010

Private Sub cmdRecon_Click()
    Dim srcTbl As ListObject, destTbl As ListObject, oNewRow As ListRow
    Dim fndRng As Range, tblRow As Long
    
Set srcTbl = Sheets("Purchase Orders").ListObjects("T_Inv")
Set destTbl = Sheets("Reconciled").ListObjects("Table5")

If Me.txtRecon = "y" And Me.txtPO <> "" Then
    With srcTbl
        Set fndRng = .DataBodyRange.Find(Me.txtPO.Value, , xlValues, xlWhole, xlByRows, xlNext, False)
        If Not fndRng Is Nothing Then
            tblRow = fndRng.Row - .Range.Row
            Set oNewRow = destTbl.ListRows.Add
            oNewRow.Range.Value = .ListRows(tblRow).Range.Value
            .ListRows(tblRow).Delete
        Else
            MsgBox Me.txtPO.Value & "  not found"
        End If
    End With
End If
End Sub

FYI:
replying to your own question (only 2 hours after asking) removed it from the "Unanswered threads" list, this was not to your advantage as board regulars check this list to seek out questions still needing answered.

A couple of members sites that are very informative regarding tables

Excel: Working with Tables (VBA)

jkp-ads.com: Excel Tables (VBA)

www.jkp-ads.com

The VBA Guide To ListObject Excel Tables

For a data analyst, Excel Tables are a necessity! They are the most efficient way to organize your raw data and refer to data that contracts or expands on a regular basis. Likewise, Excel tables can be extremely useful in combination with VBA.

www.thespreadsheetguru.com

 

[Glen M]

Perhaps something along the lines of this.
Had to change the source table name to T_Inv as T-Inv was not accepted as a valid table name in my Excel 2010

Private Sub cmdRecon_Click()
    Dim srcTbl As ListObject, destTbl As ListObject, oNewRow As ListRow
    Dim fndRng As Range, tblRow As Long
   
Set srcTbl = Sheets("Purchase Orders").ListObjects("T_Inv")
Set destTbl = Sheets("Reconciled").ListObjects("Table5")

If Me.txtRecon = "y" And Me.txtPO <> "" Then
    With srcTbl
        Set fndRng = .DataBodyRange.Find(Me.txtPO.Value, , xlValues, xlWhole, xlByRows, xlNext, False)
        If Not fndRng Is Nothing Then
            tblRow = fndRng.Row - .Range.Row
            Set oNewRow = destTbl.ListRows.Add
            oNewRow.Range.Value = .ListRows(tblRow).Range.Value
            .ListRows(tblRow).Delete
        Else
            MsgBox Me.txtPO.Value & "  not found"
        End If
    End With
End If
End Sub

FYI:
replying to your own question (only 2 hours after asking) removed it from the "Unanswered threads" list, this was not to your advantage as board regulars check this list to seek out questions still needing answered.

A couple of members sites that are very informative regarding tables

Excel: Working with Tables (VBA)

jkp-ads.com: Excel Tables (VBA)

www.jkp-ads.com

The VBA Guide To ListObject Excel Tables

For a data analyst, Excel Tables are a necessity! They are the most efficient way to organize your raw data and refer to data that contracts or expands on a regular basis. Likewise, Excel tables can be extremely useful in combination with VBA.

www.thespreadsheetguru.com

My apologies. I just jumped straight into the forum. I did not look around to see that there is an unanswered section. Thank you for looking over my issue. I will reply with my results after testing and adjusting. Also thank you for the links.

 

[Glen M]

This worked fine. THANK YOU SO MUCH! I took out the "y" part so now all my user has to do is click the reconcile button and it moves it! SO very nice! What a great way to start 2020! Thank you again!??