Copying/Deleting Rows From Sheet To Sheet

jgarth

New Member
Joined
Jul 30, 2007
Messages
24
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.
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

daniels012

Well-known Member
Joined
Jan 13, 2005
Messages
5,219
Try the macro recorder then do an auto filter, copy, paste, delete.
Then see if you need it fine tuned.

Michael
 

Datsmart

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
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

New Member
Joined
Jul 30, 2007
Messages
24

ADVERTISEMENT

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

Well-known Member
Joined
Jul 20, 2006
Messages
1,739
you may consider doing a .findnext approach

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

Code:
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

Well-known Member
Joined
Jun 19, 2003
Messages
7,985

ADVERTISEMENT

Code:
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

New Member
Joined
Jul 30, 2007
Messages
24
Code:
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

Well-known Member
Joined
Jul 20, 2006
Messages
1,739
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

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,161
Messages
5,768,547
Members
425,481
Latest member
ihumanl

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top