Copying/Deleting Rows From Sheet To Sheet

[jgarth]

Hello All,

I've done some searching, and am finding bits and pieces of what I need, but can't seem to string anything together for the final product. What I want to do is search one sheet, in column BQ, for the word Complete. If a cell has that word in it, I want to copy that row, paste it into another sheet, and than go back and delete that row, and continue the search until all rows have been searched. Is there a simple way to do this?

I was thinking of an auto filter, but I'm not sure it'd work how I want it based off of what I've read.

Thanks in advance.

 

[jgarth]

bump

 

[daniels012]

Try the macro recorder then do an auto filter, copy, paste, delete.
Then see if you need it fine tuned.

Michael

 

[Datsmart]

Need more info:
Is the word to search for the only text in the cells in column BQ or is it part of a string of text?
Where do you want the row copied to? (Sheet name)

 

[jgarth]

The Sheet I'm copying to is named Closed Applications, and the word is Just complete, but it is established from a validated data drop down box/list.

 

[brian.wethington]

you may consider doing a .findnext approach

This is a simple form of the method from the help files

With Worksheets(1).Range("a1:a500")
    Set c = .Find(2, lookin:=xlValues)
    If Not c Is Nothing Then
        firstAddress = c.Address
        Do
            c.Value = 5
            Set c = .FindNext(c)
        Loop While Not c Is Nothing And c.Address <> firstAddress
    End If
End With

 

[Datsmart]

Sub CopyAndDelete()
    Dim Rng As Range
    Dim c As Range
    Dim testvalue
    testvalue = "Completed"
    Set Rng = Range("BQ1:BQ" & Range("BQ65536").End(xlUp).Row)
    EndRow = Sheets("Closed Applications").Range("BQ1:BQ" & Sheets("Closed Applications").Range("BQ65536").End(xlUp).Row).Row + 1
    For Each c In Rng
        If c.Value = testvalue Then
            c.EntireRow.Copy Sheets("Closed Applications").Range("A" & EndRow)
            c.EntireRow.Delete
        End If
    Next c
End Sub

 

[jgarth]

Sub CopyAndDelete()
    Dim Rng As Range
    Dim c As Range
    Dim testvalue
    testvalue = "Completed"
    Set Rng = Range("B1:B" & Range("B65536").End(xlUp).Row)
    EndRow = Sheets("Closed Applications").Range("B1:B" & Sheets("Closed Applications").Range("B65536").End(xlUp).Row).Row + 1
    For Each c In Rng
        If c.Value = testvalue Then
            c.EntireRow.Copy Sheets("Closed Applications").Range("A" & EndRow)
            c.EntireRow.Delete
        End If
    Next c
End Sub

Ok, I pasted that in, and it isn't working. I changed the cell ranges to BQ instead of B and it still isn't correct. It deleted two of the Completed rows, but left one of them, and didn't copy anything to the other sheet.

 

[brian.wethington]

For Each will not work here as if you have 2 completes showing in back to back rows it will only catch the first one.

 

[Datsmart]

Sorry, my test page did not use column BQ so I had set it up with column B instead.
I edited my post, but too late for your testing.
Stand by while I re-test using BQ.