VBA Copy rows with criteria met

I am trying to create a VBA code that will find all rows on Sheet “All” that have “Completed” in 1 column (column B but A is hidden if that makes a difference).  I want it then to copy each (the entire row) of those (ideally with 1 step) and put it into a Sheet “Completed.” 

Ideally I would like it to remain on “All” but looking for a code to delete it as well (and keep on “Completed” sheet). I want it to keep the drop downs in the fields (created through data validation and those related items are what is in hidden column A).  This a document that will constantly be added to and I want it to do it automatically.  If that is not an option, I am okay with a button.  As long as it won’t duplicate fields into “Completed” sheet.

 

Here is what I have so far for a button.  It doesn’t work though because it keeps copying the same 2 rows and one of them isn’t the “Completed” criteria it needs to meet:

 

Private Sub Completed_Click()

a = Worksheets(“All”).Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To a

    If Worksheets(“All”).Cells(i, 1).Value = “Completed” Then
   
        Worksheets(“All”).Rows(i).Copy
        Worksheets(“Completed”).Activate
        b = Worksheets(“Completed”).Cells(Rows.Count, 1).End(xlUp).Row
        Worksheets(“Completed”).Cells(b + 1, 1).Select
        ActiveSheet.Paste
        Worksheets(“All”).Activate
       
    End If
Next

Application.CutCopyMode = False

ThisWorkbook.Worksheets(“All”).Cells(1, 1).Select


End Sub

By: Karen

2,214 thoughts on “VBA Copy rows with criteria met

  1. https://poboleda.org/

    I’m impressed, I must say. Seldom do I encounter a blog that’s both equally educative and interesting, and let me tell you, you have hit the nail on the head. The problem is an issue that not enough men and women are speaking intelligently about. I’m very happy I found this during my search for something regarding this.

  2. http://143.95.144.116/

    I blog often and I seriously appreciate your information. Your article has truly peaked my interest. I will take a note of your website and keep checking for new details about once a week. I subscribed to your RSS feed as well.

  3. 谷歌SEO

    Hi there! I just wanted to ask if you ever have any problems with
    hackers? My last blog (wordpress) was hacked and I ended up losing many months of hard work due to no data backup.
    Do you have any solutions to protect against hackers?

  4. Phentermine kaufen

    Great V I should certainly pronounce, impressed with your web site. I had no trouble navigating through all tabs as well as related info ended up being truly easy to do to access. I recently found what I hoped for before you know it in the least. Reasonably unusual. Is likely to appreciate it for those who add forums or something, site theme . a tones way for your client to communicate. Nice task..

  5. tokoonline88

    I’m amazed, I have to admit. Rarely do I encounter a blog that’s equally educative and engaging, and without a doubt, you’ve hit the nail on the head. The problem is an issue that not enough people are speaking intelligently about. Now i’m very happy I stumbled across this in my hunt for something relating to this.

  6. iamgutsclub.com

    Excellent blog you have got here.. It’s difficult to find high quality writing like yours nowadays. I honestly appreciate individuals like you! Take care!!

  7. hiking

    Everything is very open with a precise explanation of the challenges. It was really informative. Your site is very helpful. Thank you for sharing!

  8. moedah

    Spot on with this write-up, I seriously believe that this web site needs far more attention. I’ll probably be returning to see more, thanks for the information!

Comments are closed.