Revised macro for your particular workbook "MoveRows"

Sub Copy_With_AutoFilter1()
'Ron de Bruin......Feb. 4th, 2007
'revised by Gord Dibben Apr. 2nd, 2012
Dim ws As Worksheet
Dim WSNew As Worksheet
Dim rng As Range
Dim rng2 As Range
Dim str As String
Dim targrng As Range

Set ws = Sheets("Open")
Set rng = ws.Range("B5:IV5000") '<<<adjust to suit

str = InputBox("enter a search term")

ws.AutoFilterMode = False

'starting at column B the Field is 3....Person
rng.AutoFilter Field:=3, Criteria1:=str

Set WSNew = Sheets("Closed")
Set targrng = WSNew.Cells(Rows.Count, 2).End(xlUp) _
.Offset(1, 0)
ws.AutoFilter.Range.Copy
With targrng
.PasteSpecial Paste:=8
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
End With

ws.AutoFilterMode = False
End Sub



Gord



On Mon, 2 Apr 2012 08:55:30 +0000, cwm7102
wrote:


I need help to find a value or name in a column and move all rows
containing that value to another worksheet. It will be good if there's
an input box to key in the value to search. In my excel file in
"MoveRows" in the Open worksheet the value or rather the name to be
search is in column D titled Person. I would like to find all rows with
say John in Col D and move it to the next blank row ie row7 in Closed
worksheet.


+-------------------------------------------------------------------+
|Filename: MoveRows.zip |
|Download: http://www.excelbanter.com/attachment.php?attachmentid=316|
+-------------------------------------------------------------------+