Copy cell value to another sheet and paste into cell (D) of row containing sheet name

[JasonBing]

I am going around in circles!!! Please help!!

I have a sheet called "FTBJOB2" containing a list of job numbers in column B and the "job status" in column D.

With each new job a new sheet is created from a template. This sheet is named with the job number from the list in column B of sheet "FTBJOB2"

Contained in each job's sheet is the job status contained in cell "Y2"

What I need is a code that, each time the job status in the job sheet (in "Y2") is changed, that job status change is reflected in column D of "FTBJOB2" in the row containing the job number

I brain is telling me that the code has to;
1. Search column B of "FTBJOB2"
2. Find the ROW with the sheet name (of the job sheet)
3. Copy cell "Y2" from the job sheet and paste it in column D of the row containing the job sheet name in column B

I have tried a lot of different things and I have had no success

My brain is also saying this has to happen in a worksheet change event.

I have reached the limits of my brain and need someone who is more capable to assist me

pleeeeeease and

Thank you

Jason Bing

 

[JasonBing]

Oh I almost forgot. The sheets are all protected. Not with a password, just to prevent people inadvertently changing things


Thanks wonderful people

Jason Bing