Move rows to another worksheet

cwm7102

New member
Joined
Apr 2, 2012
Messages
1
Reaction score
0
Points
0
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.
 

Attachments

  • MoveRows.xlsm
    35 KB · Views: 85

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
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
 
Top