Find, cut, and paste entire row

[Jaybird]

Howdy. Based on the value of an ID number scanned into an input box,
I would like VBA to search for it on one column on one worksheet, then
if found cut the entire row and paste it to the end of a list on
another worksheet. I currently find the ID number using this method:

myvalue = Columns("H").Find(InputBox(PPID))

Works great, but I can't figure out how do determine what row this is
found on. If I knew that, I would attempt to cut or copy the entire
row. Any clues?

John

 

[Mike Fogleman]

Find returns a range object, so treat it like a range:

Dim myvalue As Range
Dim LastRow As Long
LastRow = Sheet2.Cells(Rows.Count, "A").End(xlup).Row
Set myvalue = Columns("H").Find(InputBox(PPID))
myvalue.EntireRow.Cut Destination:= Sheet2.Range("A"& LastRow + 1)

Mike F

 

[john]

Find returns a range. I added to your code & hopefully, will give you further
ideas to do what you want.


Sub MoveData()
Dim myvalue As Range
Dim LastRow As Long
Dim nextsh As Worksheet
PPID = "Input ID"
Set nextsh = Worksheets("Sheet2") '<change as required
With ActiveSheet
Set myvalue = .Columns("H").Find(InputBox(PPID))
'cancel
If myvalue.Value = "" Then Exit Sub

If myvalue Is Nothing = False Then
'value entered found
LastRow = nextsh.Cells(Rows.Count, "H").End(xlUp).Row + 1
myvalue.EntireRow.Cut Destination:=nextsh.Range("A" & LastRow)
Else
MsgBox "ID Not Found"
End If
End With
End Sub

 

[Jaybird]

Thank you so much! This is great! I will attempt to implement this
tonight...

 

[Jaybird]

How can I get this module to delete the blank row left over from the
cut and paste?

 

[john]

updated as requested:

Sub MoveData()
Dim myvalue As Range
Dim LastRow As Long
Dim nextsh As Worksheet
PPID = "Input ID"
Set nextsh = Worksheets("Sheet2") '<change as required
With ActiveSheet
Set myvalue = .Columns("H").Find(InputBox(PPID))
'cancel
If myvalue.Value = "" Then Exit Sub

If myvalue Is Nothing = False Then
'value entered found
myrow = myvalue.Row
LastRow = nextsh.Cells(Rows.Count, "H").End(xlUp).Row + 1

myvalue.EntireRow.Cut Destination:=nextsh.Range("A" & LastRow)
.Cells(myrow, 1).EntireRow.Delete

Else
MsgBox "ID Not Found"
End If
End With
End Sub

 

[Jaybird]

Man, that is just slick as all get out! Thanks so much for
everything!