Find, cut, and paste entire row
 

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

Find, cut, and paste entire row
 

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
"Jaybird" wrote in message
...
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

Find, cut, and paste entire row
 

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
--
jb


"Jaybird" wrote:

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

Find, cut, and paste entire row
 

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

Find, cut, and paste entire row
 

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

Find, cut, and paste entire row
 

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
--
jb


"Jaybird" wrote:

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

Find, cut, and paste entire row
 

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