Code to compare columns in two sheets and copy row if there is a specific text in a cell

[netrixuser]

Hello all,
I apologise up front because I haven't got my own code to show for you to debug or correct - I simply cannot get my head around how to start with this problem.
To simplify my issue:
One Workbook containing sheet1 and sheet2
Sheet1 contains a list of circa 5000 alerts received by a vendor, column B has either NEW, EXISTING or CLOSED. Column C has a unique reference number (no text) Column P has the End Time of the alert which is only populated if the alert is CLOSED.

The alerts are initially received via an email (trying desperately to get the vendor to send as a csv, but that is a different battle!) and sheet 1 is populated by copy/paste from the email text. I insert a columns at Column A to insert the date
When an alert is CLOSED and hence column O populated with the date/time the whole list of (currently) 5000 alerts is resent as another email (even the NEW and EXISTING alerts). I copy/paste the (now) CLOSED alerts into sheet2, using Column C (unique ref) as the guide to the copy/paste.

My goal would be to paste the list of all alerts into sheet 2 and then run a piece of code that does the following:

For sheet 2 -

1. look at Column A for the text CLOSED​

2. When found, note the ref number in Column C.​

3. Copy the Row (columns A through O)​

4. Paste those cells in Sheet 1 in the row with the appropriate ref number (noted in step 2) Note: Column A in sheet1 has the date of the alert so the paste would be from Column B (for the appropriate ref number)​

​

For Steps 3 and 4, it is only columns A and O that would contain different data to that in sheet1, but I'd assume copying the whole row would be easier.

Sheet1 example:

Sheet2 example

As always, many thanks for any help you can give, which in this case will be a lot !!
Netrix

 

[netrixuser]

So I have some code now, I have worked out how to loop through the sheet2 column [now called Scratch] to find the text CLOSED, and also to assign the Unique ref number, from Column C to a variable named SrcID.

I am now stumped for the next bit - I need to look through Column D of sheet1 [now called C9 NSP Alerts] for a matching SrcID, when found I need to copy the entire row from sheet2 [Scratch] to the matching row in shhet1 [C9 NSP Alerts], but pasting form column B (sheet 1 contains the date in Column A which is not present in sheet2)
my code so far is: (some Variable declarations omitted - there are lots at present as been try lots !!)

Dim Lrow_input As Integer 'input sheet is "Scratch" ie. new email with CLOSED alerts
Dim Lrow_output As Integer 'output sheet is "C9 NSP Alerts"

Dim ws_input As Worksheet 'Scratch
Dim ws_output As Worksheet 'C9 NSP Alerts

Set ws_input = Worksheets("Scratch")
Set ws_output = Worksheets("C9 NSP Alerts")
Set SearchRange = ws_output.Range(Range("D6"), Range("D6").End(xlDown))

    With ws_input
        Lrow_input = .Cells(.Rows.Count, 1).End(xlUp).Row 'get last row of data
    End With
    
    With ws_output
        Lrow_output = .Cells(.Rows.Count, 1).End(xlUp).Row 'get last row of data
    End With

    'Worksheets("Scratch").Select
    With ws_input

    Rw = 2
    For counter = 1 To 5 'will bereplaced with  For counter = 1 To Lrow_input
    
            If Cells(Rw, 1) = "CLOSED" Then
                'MsgBox "test"
                SrcID = Cells(Rw, 1).Offset(0, 2).Value 'gives me the unique ref ID of the row containing CLOSED
                         
            End If

    Rw = Rw + 1
    counter = counter + 1
    
    Next counter
    
End With
End Sub