Help needed with VBA code to replace values with names and delete others Values

[rv02]

Dear All

I really need help with this long winded process and am hoping someone can give me a code to be able to do what I need automatically.

I have attached a very simple workbook to explain what I need to do.
In column A I have cells that have RXXXX [Role] I need to replace that with the name above that cell which is in the format of AAA,BBB (1000).

Basically everytime I hit a cell in column A which is Rxxxx where xxxx is a number then replace it by the name above it which is in the format of AAA,BBBBB (xxxx) where xxxx is number. Then I need to copy hat name for all the blank cells below it if there is data for them in the corresponding Col B.

For example in the workbook enclosed I need to replace R14317 Sales Manager with Aaa,BBB (1000) and R14217 Sales Manager to be replace by CCC,DDD (1001) and then copy CCC,DDD (1001) all the way down from A11 to A17 and then R14317 to be replace by EEE,FFF (1002) and so on.
If possible I then need to delete cells in Col A that begin with BU_zzz (where zzz is a text such as access or fashion etc.), i.e. in Col A I want to delete A1 to A4 and then start the process o copying the criteria above.

This might be confusing but please let me know if I need to give further explanation. My actual work book has over 100 entries in the same format and currently its taking me over two hours to do this manually.

Any help is much appreciate it.