How to set excel to move specific cells to a new sheet when a date reached

[Albert Dirk]

Good day

Can someone please help me I am using office 2010?

I want Excel to copy some cells from the first sheet “Training Matrix” to the second sheet “Expired Medicals” automatically when the medical has expired (Colom F. the cells that must be copied from the first sheet must be as follows " employees Clock Number, Employees Name, ID Number, Occupation and medical Date" and only these cells must be copied and I don't want any information on the first sheet to be changed in any way.

I have tried to attach the excel document but could not, so I am trying to attach the screenshots that I have taken, I hope it works though.

Training Matrix Sheet.jpg

Expired Medicals Sheet.jpg

[alansidman]

To attach a file:

Click on GO ADVANCED and then scroll down to Manage AttachmeTo nts to open the upload window (or use the paperclip icon).

[Albert Dirk]

I did try to google it first but I don't know very much about macro's that's why I asked the question here. okay I hope the document is attached now.

[alansidman]

Your expired sheet and your first post are not in sync. Please clarify which cells in training matrix should be copied to expired sheet. The Picture in the first post does not look like the sheet in the workbook.

[alansidman]

I think this will work for you. Place this code in the Workbook Open Event. It will automatically check the date and if this EE has been previously updated when you open the workbook.

Please Login or Register  to view this content.

[Albert Dirk]

thanks for your help, can you please just help me so that when the medical is redone and the new date is entered in the ("TRAINING MATRIX") sheet that the line for that employee will be removed automatically from sheet 2 ("Expired Medicals")?

can you also help me so that the ("TRAINING MATRIX") sheet will be moved to the ("Expired Medicals") but only up to a certain line lets say for example line 65?

[alansidman]

Albert, from the business perspective, an employee could be re-entered in the training matrix a second or third time? Is my understanding correct?

Additionally, I do not understand your statement about nothing after line 65, could you clarify with a business example as your current explanation is confusing?

[Albert Dirk]

Okay I have added the document again 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 employees 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 there new medicals before it expires.

The code that I have so far is:

Please Login or Register  to view this content.

Thanks for all the help so far

Moderator Comment: I have added code tags to your thread. Please read the forum rules carefully and abide by them in the future.

[alansidman]

Since this is a new issue separate from the original, I would urge you to create a new thread to get maximum exposure to the forum with the new issue. What you are asking is out of my wheelhouse and you would be better off with a fresh approach and mark this thread as solved as it covered your original request.