Need autorun Macro that copies data from Master sheet to second sheet based on condition

I have been tasked with creating an excel file that has a master sheet, which automatically copies select cells within a row (not the entire row) based on the value of a cell within the row. I have found tons on macros that copy the entire row, but I am struggling with how to identify within the code which cells to copy and how to get them to their appropriate destination on a second worskheet.

It would also be nice to have this macro run automatically as soon as the value Y (from a list) is entered, rather than using a short cut. (The people that will be using the file are not computer savy.)

I have attached a sample of the file I will be creating. The data thats entered into the columns will need to be copied to the other sheet if the user selects y in column Y.

Any help would be greatly appreciated.

Thanks

Update: The macro runs great, however we are running into a little bit of a problem. If the answer in column Y is changed to from Y to N, the data still stays on the CMS sheet even though the answer is now N. Also, for example if that same line of data is then changed backd to Y (in column Y), a duplicate entry of that line of data is then added to the CMS sheet. Is there an easy way for me to fix this?

Thanks again