Excel VBA to check other sheet and copy over information

Sebastian2021

New Member
Joined
Mar 3, 2021
Messages
1
Office Version
  1. 2010
  2. 2007
Platform
  1. Windows
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.

VBA Code:
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
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Watch MrExcel Video

Forum statistics

Threads
1,129,684
Messages
5,637,790
Members
416,982
Latest member
lisam77

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top