VBA code not recognising all filled rows

Absfml

New Member
Joined
Mar 5, 2019
Messages
2
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:
Code:
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
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,500
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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:

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

Absfml

New Member
Joined
Mar 5, 2019
Messages
2
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.
 
Last edited by a moderator:

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,500
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,816
Messages
5,525,061
Members
409,617
Latest member
Lenaf

This Week's Hot Topics

Top