Need help setting up a custom macro!!

I am very new to macros and was wondering if it is possible to have a "Master" sheet of questions broken down to different sheets for each responsible party needed to answer. Further have the answers be color coded to be moved to a final sheet. Then have each of the final sheets consolidated to one "Final Sheet".

The attached spreadsheet shows what I am looking to do - I hope it is not too confusing - Thanks for any help!!!