Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find, cut, and paste entire row
Thank you so much! This is great! I will attempt to implement this
tonight... |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find, cut, and paste entire row
How can I get this module to delete the blank row left over from the
cut and paste? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find, cut, and paste entire row
Man, that is just slick as all get out! Thanks so much for
everything! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I copy and paste an entire workbook? | Excel Worksheet Functions | |||
Find Multiple Values, Copy Entire Row & Paste | Excel Programming | |||
Find/Copy/paste.. then Find/Paste - not working ... at all.... | Excel Programming | |||
Cut and Paste entire row from one worksheet to another | Excel Worksheet Functions | |||
Cut and Paste entire row from one worksheet to another | Excel Worksheet Functions |