Excel VBA to check other sheet and copy over information

[Sebastian2021]

Hello,

I am new to VBA and I am completely stuck on this code. I've tried loads of different threads and methods but with no luck. Please help!

Bit of background. One sheet holds customer info and a date of when a letter has been issued, a column to check the number of weeks since issued (formula already set up), and another column to add a date once the letter has been returned.

My spreadsheet contains several sheets but the 2 I am using for this code are called "CHECKLIST" & "REMINDERS". I want the code (which I intend to have a button for on the REMINDERS sheet) to see if a reference from "CHECKLIST" column B is in "REMINDERS" column B (they won't be in the same order). If it is, then do nothing/ move to the next one. If it is not, then I need to check if the data in the row (with the new reference) matches some criteria. These being if a date is in CHECKLIST Column H, number of weeks >3 in column I and nothing in column J (this is where the response date would be held if we had had one). If these are all true then I want 8 cells from the row copied from CHECKLIST on to the REMINDERS sheet.

I am planning to have it so the user can hit the command button to see which customers have not yet responded after 3 weeks so that a reminder can be issued. If they hit the button and there are no new cases then a message to say "No new reminders required" would be great!

I have the following formula but after hours of trying I cannot get it to work.

Sub Reminder()

Dim i As Long, mydate As Date, irow As Long, lastrow As Long

ThisWorkbook.Sheets("CHECKLIST").Activate

irow = Sheets("CHECKLIST").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
lastrow = Sheets("REMINDERS").Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To lastrow

mydate = Sheets("CHECKLIST").Cells(i, 8)
response = Sheets("CHECKLIST").Cells(i, 10)
weeks = Sheets("CHECKLIST").Cells(i, 9)

If mydate > "31/12/2020" And IsDate(response) = False And weeks >= 3 Then

    With Sheets("REMINDERS")
    .Range("A" & irow) = Sheets("CHECKLIST").Cells(i, 1).Value
    .Range("B" & irow) = Sheets("CHECKLIST").Cells(i, 2).Value
    .Range("C" & irow) = Sheets("CHECKLIST").Cells(i, 4).Value
    .Range("D" & irow) = Sheets("CHECKLIST").Cells(i, 5).Value
    .Range("E" & irow) = Sheets("CHECKLIST").Cells(i, 6).Value
    .Range("F" & irow) = Sheets("CHECKLIST").Cells(i, 7).Value
    .Range("G" & irow) = Sheets("CHECKLIST").Cells(i, 8).Value
    .Range("H" & irow) = Sheets("CHECKLIST").Cells(i, 9).Value

    End With

    End If

Next i

End Sub

Massive thanks in advance!
Cheers
Seb