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. I have attached my file. In my excel file in 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.
Move rows to another worksheet
- Thread starter cwm7102
- Start date
- Joined
- Mar 21, 2011
- Messages
- 1,960
- Reaction score
- 0
- Points
- 36
- Excel Version(s)
- O365
You put the name to filter on in D1 and use this code
Code:
Sub MoveData()
Dim rng As Range
Dim rng2 As Range
Dim lastrow As Long
With ActiveSheet
lastrow = .Cells(.Rows.Count, "D").End(xlUp).Row
Set rng = .Range("D4").Resize(lastrow - 3)
rng.AutoFilter field:=1, Criteria1:=.Range("D1").Value
On Error Resume Next
Set rng2 = rng.Offset(1, 0).SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not rng2 Is Nothing Then
rng2.EntireRow.Copy Worksheets("Closed").Range("B4").End(xlDown).Offset(1, -1)
End If
rng.AutoFilter
Set rng = Nothing
End With
End Sub