Move a whole row from one sheet to next available row in another sheet based on cell value

Hi, hoping someone can help me with this.

I've got a spreadsheet that currently keeps track of all employees and their qualifications. It already has a macro running in the background to automatically chance cell colour based on the expiry date of their qualification.

So far I've had all employees, past and current, on the same sheet, but now I want to create a second sheet for ex-employees, and have inserted a new column that asks if the employee is current with responses restricted to yes or no. I'd like the macro set up so that when 'no' is selected the whole row is automatically removed from that sheet and moved to the next available row on the ex-employee sheet. I want it set up so that this whole process happens without having to manually run the macro.

The macro that was written to change the cell colour automatically was already in the spreadsheet when I was given it, and I found some code online to move rows from one sheet to another and have tried to adapt it into my workbook but it's not working the way I want it to. It inserts the rows from row 501 onwards, when the first available row in the second sheet should be row 3. It also doesn't run automatically - I have to manually run the macro before it'll work.

These are the codes I'm running with:

Sheet 1:

Please Login or Register  to view this content.

Module 2:

Please Login or Register  to view this content.

Any help would be greatly appreciated.

It sure does, thanks mate! Would there be a way I could get Excel to automatically arrange the rows alphabetically based on the data in call A?