Macro to cut a row from one Worksheet and paste in another

I have created a workbook that comprises of 6 worksheets and I wish to create a Macro so that a row is automatically cut from one worksheet and pasted to another once the Status column changes.

The Status column (E) in worksheet 'Infrastructure CERs & RFCs' uses a lookup table to set the status depending on the information input into the row. What I want is for the whole row to be automatically cut and pasted to another worksheet called 'Released RFCs' once the Status changes to 'Released'.

Similarly, if the status changes to 'Closed' or 'Withdrawn' then I want this row to be automatically cut and pasted to the 'Closed or Withdrawn Changes' worksheet.

Also, the spreadsheet is shared and I can't protect the worksheets because I need to cut and paste rows to altrenate sheets, as above. If I do create a Macro will I then be able to protect certain cells?

Please note that I have never used VBA before. Any help much appreciated.

Hi

This site will get you started. Once you have figured out the filter part then upload a sample file and we will look at the automatic part

http://www.rondebruin.nl/copy5.htm#Add

VBA Noob

Please read forum rules below and then wrap your code

VBA Noob

Try

Please Login or Register  to view this content.

VBA Noob

Thanks for your help, worked a treat! However, for some reason once the sheet is protected and the box is ticked to allow Auto Filter, after the Macro is run I can no longer use Auto Filter unless I unprotect the sheet again. Any suggestions?

Also, when using the countif formula how can I count information from two worksheets?

Finally, I have another worksheet that I wish from time to time to be able to delete the entire row. Again, I want the sheet to be protected but I don't want the data to be cut and pasted elsewhere, just deleted from worksheet altogether. How could I achieve this?