VBA to copy to new sheet and paste to last empty row based on criteria

Hi all,

I'm trying to use a macro to copy data from sheet A to sheet B based on a criteria

as attached in this scenario i would like for it to review column C in sheet 1 (Master data) for anything that shows the word "query" then copy that entire row into the first blank row of the second sheet (Queries)

there is some bad code in there and it does find the right rows to move but it puts them in adjacent position to where they are in sheet 1 which is not the required result

the end result i want to load new data daily into sheet1 approx 25000 rows with expected queries less than 100 and so each day it will add anything as query to sheet 2 so this list will get bigger over time recording all the queries


Many thanks in advance for your help

.
One method :

Sub CopyYes()
   Dim c As Range
    Dim j As Integer
    Dim Source As Worksheet
    Dim Target As Worksheet
    Application.ScreenUpdating = False
    ' Change worksheet designations as needed
    Set Source = ActiveWorkbook.Worksheets("Master Data")
    Set Target = ActiveWorkbook.Worksheets("Queries")
    
    Application.ScreenUpdating = False
   
    j = 2    ' Start copying to row 2 in target sheet
    
    For Each c In Source.Range("C:C")   ' Do 1000 rows
        If c.Value = "query" Then
           Source.Rows(c.Row).Copy Target.Rows(j)
           j = j + 1
        End If
    Next c
      
    Application.CutCopyMode = False
    
    Application.ScreenUpdating = True
End Sub

Thank you for your help, i really appreciate it. ive added it to my spreadsheet but it is copying over existing rows when pasting to sheet 2 "queries" is there a way to make it so each time the macro is run it will paste to the bottom of the data that already exists in "Queries" to the first blank row?

so for example i drop the data in the master sheet i pick up 2 queries so when the macros finished sheet 2 "queries will have 2 rows of data. then tomorrow i drop my new data in and pick up 3 queries so the macro will then add it to what was there today and there is now 5 rows of data etc hopefully that makes sense!

.

Sub cpyPaste()

Dim i As Integer
   
For i = Cells(Rows.Count, "A").End(xlUp).Row To 2 Step -1
    If Sheets("Master Data").Range("C" & i).Value = "query" Then
        Sheets("Master Data").Rows(i).Copy Sheets("Queries").Range("A" & Rows.Count).End(xlUp).Offset(1)
    End If
Next i

End Sub

Hi Logit, i do have one question however i have added this to my sheet and it works well where the sheet is format as the example however i tried to adapt it to look at something else in column T on the same spreadsheet but when i change

If Sheets("Master Data").Range("C" & i).Value = "query" Then

to

If Sheets("Master Data").Range("T" & i).Value = "query" Then


then it does not work all other parameters remain the same so the simple fix of changing the column letter does not work can i bother you one more time for your help to understand how i can make it look to a different column

thank you

Thank you Logit thats amazing, you nailed it!

I really appreciate your help

.
Hmm ... not certain what to advise. I made the change here to column T and it functions well.

Option Explicit

Sub cpyPaste()

Dim i As Integer
   
For i = Cells(Rows.Count, "A").End(xlUp).Row To 2 Step -1
    If Sheets("Master Data").Range("T" & i).Value = "query" Then
        Sheets("Master Data").Rows(i).Copy Sheets("Queries").Range("A" & Rows.Count).End(xlUp).Offset(1)
    End If
Next i

End Sub

I presume you are still seeking the term query ?

If the term has changed you will need to change it in the macro code as well.