Cutting row is one cell has value "Yes"

[Rashie]

Hi All,

Hoping you can help.
I've got a workbook which keeps breaking.
It consists of quite a few coloums and about 20,000 rows. I'm trying to make it so when there is a value of "Yes" in range X on sheet "Flightlist" it cuts the entire row that the "Yes" value is on and pastes it another worksheet called "Completed."
I also need this to be ongoing so every time the macro is run it won't overwrite the information already on sheet completed, so it will just go underneath what already exists.

Hoping you can help, i'd give you my current VBA code but honestly it's total scrap. Using Excel 2010.

Thanks!!

 

[vcoolio]

Hello Rashie,

As a start, the following code may work for you:-

Sub Copy()

Application.ScreenUpdating = False

    Dim lRow As Long

lRow = Range("A" & Rows.Count).End(xlUp).Row

Sheets("FlightList").Select

For Each cell In Range("A2:A" & lRow)
    If cell = "Yes" Then
    cell.EntireRow.Copy
    Sheets("Completed").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
    cell.EntireRow.ClearContents
    End If
Next

Columns("B").SpecialCells(4).EntireRow.Delete
Application.ScreenUpdating = True
Application.CutCopyMode = False
Sheets("Completed").Select

End Sub

It searches Column A in the "FlightList" sheet for the criteria "Yes" (case sensitive) and transfers the relevant row to the "Completed" sheet.
It also clears the "used" data from the "FlightList" sheet after it has been transferred as I assumed that you would have no further use for the "used" data.

Attached is my test work book for you to peruse. Just type as many "Yes" criteria as you want in Column A of the "FlightList" sheet to have the relevant rows transferred to the "Completed" sheet.

https://www.dropbox.com/s/itgj2ohqvp21ayt/Rashie.xlsm?dl=0

I hope that this helps.

Cheerio,
vcoolio.

 

[Rashie]

Hi Vcoolio,

You are a saint. I spent half my morning messing about with this. A bit of tweaking and it worked perfectly.
Thanks so much for your help, you're a dude.

Thanks,

Rich :D

 

[vcoolio]

Hello Rashie,

You're welcome. Glad that I could help.

Cheerio,
vcoolio.