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
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Excel Version
    You put the name to filter on in D1 and use this 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
            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