Removing all rows with word yes populated and pasting to new sheet

Hi

Im wondering if you can help with what im trying to achieve.

I have a spreadsheet with a list of tasks on it (around 600 rows in excel)

Every cell in column AD is currently populated with the word NO.

Once each task is completed the word YES is typed into each row starting on Cell AD7.

I then was looking to have a macro button that we can press at the end of each day and it removes all the rows which have a Yes populated in column AD and paste them into a seperate sheet called "Completed Tasks" so it leaves only the outstanding tasks on the sheet "Outstanding Tasks"

These tasks can be completed in any order so cell AD450 could be populated with a YES and cell AD10 could still be sitting as NO.

Can this be achieved? Can someone help me out on this.

Cheers

McCrimmon

McCrimmon,

Give this a try and see if it does what you are wanting.

Starts at the bottom of the Outstanding Tasks sheet and looks at each row up through row 7. If the value in Column AD of that row is equal to "YES" then that row is moved to the Completed Tasks sheet and deleted off of the Outstanding Tasks sheet.

Sub MoveComplete()

Dim lOTLastRow As Long, lCTLastRow As Long, lRow As Long
Dim wsOT As Worksheet, wsCT As Worksheet

Set wsOT = Sheets("Outstanding Tasks")
Set wsCT = Sheets("Completed Tasks")

lOTLastRow = wsOT.Cells(Rows.Count, 1).End(xlUp).Row
lCTLastRow = wsCT.Cells(Rows.Count, 1).End(xlUp).Row + 1

For lRow = lOTLastRow To 7 Step -1

    If UCase(wsOT.Cells(lRow, 30)) = "YES" Then
        wsOT.Range("A" & lRow).EntireRow.Copy
        wsCT.Range("A" & lCTLastRow).PasteSpecial
        wsOT.Range("A" & lRow).EntireRow.Delete
        lCTLastRow = lCTLastRow + 1
    End If
    
Next lRow

End Sub

Beautiful work my friend!

Works a treat!

Im in the process of learning VBA so hopefully in the next month or so I will be up to scratch and not be posting on this forum unless its to help someone else

McCrimmon,

Wonderful! I'm glad it worked and I could be of service.

Just out of curiousity, where are you located. I always find it interesting to see where in this small world questions come from.

Originally Posted by boylejob

McCrimmon,

Wonderful! I'm glad it worked and I could be of service.

Just out of curiousity, where are you located. I always find it interesting to see where in this small world questions come from.

Edinburgh in Scotland.

Jeff - Could you do me a favour if possible and provide me some information as to what each line does? As I said above, im new to VBA and still trying to find my feet with the who program.

Thanks again

Much appreciated!

McCrimmon,

Here is the code description that you asked for. I hope it all makes since, but if it doesn't, please don't hesitate to ask me about anything you don't understand.

Sub MoveComplete()

'The Dim statements define what you want the variable to be.
'Undefined variable automatically become the Variant type and
'code with all Variants does not run efficiently.  I have my
'VB editor set up so that it forces me to define all my variables
Dim lOTLastRow As Long, lCTLastRow As Long, lRow As Long
Dim wsOT As Worksheet, wsCT As Worksheet

'Here I am assigning the two worksheets to a variable which makes
'coding easier and also keeps me from have to use the Select
'statement which ultimately slows down your code
Set wsOT = Sheets("Outstanding Tasks")
Set wsCT = Sheets("Completed Tasks")

'These next two line find the last row with information on each
'of the sheets.  I add 1 to lCTLastRow to give me the first blank
'row.  This is where we begin putting the complete data.
lOTLastRow = wsOT.Cells(Rows.Count, 1).End(xlUp).Row
lCTLastRow = wsCT.Cells(Rows.Count, 1).End(xlUp).Row + 1

'This is a For Next loop and I am telling it to begin with the last
'row and work up through each row through row 7.  The Step -1 is
'indicating to move upward.  Step 1 would be a downward movement. I
'am moving backwards because we are deleting rows.  Example: If I were
'moving down and I am on row 300, I move that row and then delete it.
'Row 301 now becomes 300 but the For Next is going to move to the next
'Row which is 301 so you will end up missing a row.  If we are working
'backwards and we delete row 300, row 301 still becomes row 300 but we
'have already looked at 301 and the next row we will be looking at is 299.
For lRow = lOTLastRow To 7 Step -1
    'Looking at Column 30 or AD for whatever row we are on.  The UCase just
    'makes sure that all the letters are capitalized which accounts for
    'someone mistakenly entering Yes instead of YES.
    If UCase(wsOT.Cells(lRow, 30)) = "YES" Then
        'If YES is in Col 30, the whole row is copied off the Outstanding Task
        'Sheet
        wsOT.Range("A" & lRow).EntireRow.Copy
        'Whole row is pasted onto the Completed sheet. lCTLastRow represents
        'the next available blank line
        wsCT.Range("A" & lCTLastRow).PasteSpecial
        'Whole row is deleted of the Outstanding Task sheet
        wsOT.Range("A" & lRow).EntireRow.Delete
        'Next blank row on Completed sheet is advanced by one
        lCTLastRow = lCTLastRow + 1
    End If
'Goes to next row and loops back up to the For lRow = ...
Next lRow

End Sub

I have been programing in VBA for about 7 or 8 years now. I just recently found this forum and it is absolutely fantastic. It is amazing what I have learned and how much better my code it now. If you are not sure about something don't hesitate to post to this list. There are some very talented folks monitoring this forum and you will almost always get an answer.

I started out asking questions and then decided to see if I could answer a few. Mudraker told me that answering question helped him learn even more and he was absolutely right. I have learned all kinds of new things simply by answering questions. There are often several ways of accomplishing the same task and by asking a question you might find an easier way of doing something. So ask question!!!!