Results 1 to 2 of 2

Thread: Move rows to another worksheet

  1. #1

    Move rows to another worksheet



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.
    Attached Files Attached Files

  2. #2
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •