VBA code not recognising all filled rows

[Absfml]

Afternoon
First post so please bear with me, and a bit of a beginner with VBA codes.

I'm trying to use a VBA code to identify all rows on a worksheet that contain the word 'No' in a certain column, then copy and paste these rows into a separate worksheet. This worked with the initial set of data on the master worksheet, but this data is getting added to every month, and now the code is not picking up all the rows required. I have 20 rows that show 'No' in the specified column, but only 10 of these are transferring to the new worksheet. It's not that the code isn't reading the newly added data, as some of this is showing correctly once the codes run.

Would really welcome any suggestions. This is what I'm using:

Private Sub CommandButton1_Click()




Worksheets("Failed QC's").Activate
Worksheets("Failed QC's").Range("A2:DF1500").ClearContents




a = Worksheets("Master form").Cells(Rows.Count, 1).End(xlUp).Row


For i = 2 To a




Worksheets("Master form").Activate


If Worksheets("Master form").Cells(i, 92).Value = "No" Then


Worksheets("Master Form").Rows(i).Copy
Worksheets("Failed QC's").Activate
b = Worksheets("Failed QC's").Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("Failed QC's").Cells(b + 1, 1).Select
ActiveSheet.Paste
Worksheets("Master form").Activate


End If
Next


Application.CutCopyMode = False
ThisWorkbook.Worksheets("Master form").Cells(1, 1).Select


End Sub

 

[RoryA]

Welcome to the forum.

Is there always data in column A for each row?

You could do it with an autofilter, but if you want to loop, perhaps this:

Private Sub CommandButton1_Click()

    Worksheets("Failed QC's").Range("A2:DF1500").ClearContents
    Dim outRow As Long
    outRow = 2

    With Worksheets("Master form")
        Dim lastRow As Long
        lastRow = .Cells(.Rows.Count, 1).End(xlUp).Row

        Dim i As Long
        For i = 2 To lastRow

            If UCase$(.Cells(i, 92).Value) = "NO" Then

                .Rows(i).Copy Worksheets("Failed QC's").Cells(outRow, 1)
                outRow = outRow + 1

            End If
        Next

        Application.Goto .Cells(1, 1), True
    End With

End Sub

 

[Absfml]

Thankyou! The first 3 columns are normally hidden as the data is irrelevant but on checking blanks which explains it.
Will try with your code though- thanks again- very helpful.

 

[RoryA]

That's probably the issue then as your code (and mine) is using column A to determine the last row of data. Change it to use column D, or another column that is always populated.