Move specific cells to a new sheet when a date is reached or 30 days from being reached

Okay I have added the document you will see that at the bottom of the ("TRAINING MATRIX") sheet there is additional information where I have to sign the document after I have printed it but it must only stay at the ("TRAINING MATRIX") sheet (at the moment it moves the information to the ("Expired Medicals")sheet. The date that expires on the ("TRAINING MATRIX") sheet is the employee’s yearly medical that they must do every year so if the date is updated in the ("TRAINING MATRIX") sheet the name must be automatically removed from the ("Expired Medicals") sheet but must remain in the ("TRAINING MATRIX") sheet without making a second entry for that specific employee. The code must run while I am working with the document at the moment I have to run the code manually. You will also see on the ("Expired Medicals") sheet there appears to be a Colom G that must not be there. If it is possible I will also prefer it if the employees information can be copied to the ("Expired Medicals")sheet 30 days from expiration date and not when it has expired so that it gives me enough time to book them to go and do their new medicals before it expires.

If someone can help me I will appreciate it?

The code that I have so far is:

Private Sub Workbook_Open()
Dim lr As Long, lr2 As Long
Dim s1 As Worksheet, s2 As Worksheet
Set s1 = Sheets("TRAINING MATRIX")
Set s2 = Sheets("Expired Medicals")
lr = s1.Range("A" & Rows.Count).End(xlUp).Row

Application.ScreenUpdating = False

For i = 4 To lr
lr2 = s2.Range("A" & Rows.Count).End(xlUp).Row
If s1.Range("H" & i) <= Date Then
Dim Res As Variant
On Error Resume Next
Err.Clear
Res = Application.WorksheetFunction.VLookup(s1.Range("B" & i), s2.Range("B1:B" & lr2), 1, False)
If Err.Number = 0 Then
Resume Next
Else
s1.Range("A" & i & ":H" & i).Copy s2.Range("A" & lr2 + 1)
End If
End If
Next i
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

With this code in sheet Activate

Please Login or Register  to view this content.

new dates in sheets Training Matrix, keeps the other sheet always up to date

Kind regards
Leo

thanks for the quick reply

I tried to copy your code into my document but then it doesn't work, but when I try to use yours it works I don't understand it will you please enter the code for me in my document so that I can use mine, because I have more data and tabs in my document and I need to keep the data and tabs. you will see it looks almost a 100% the same.

Sheet Training Matrix is not the same like before, thats wy

solution

Please Login or Register  to view this content.

Kind regards
Leo

Thanks for all of your help I really appreciate it allot. The document you sent me back works perfectly except for two things.

1. I can’t set the cells in the ("Expired Medicals") sheet where it shows the date to actually show the date and not a bunch of numbers (if you do set it when you go to another tab and then come back to the tab the date is gone and the numbers appear again).

2. I can't set the cells in the ("Expired Medicals") sheet to be in the middle or left (if you do set it when you go to another tab and then come back to the tab the cell alignment is as it was again).

Please if you can help me with these last things the document will be perfect...

In code change this

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Kind regards
Leo

thanks for all of your help, it works perfectly now...