Copy & Paste to a specific row in another worksheet and clearing data from the form

You can upload your file by clicking 'Go Advanced' at the bottom of the reply box, then click 'Manage Attachments' and follow the menu. This will allow us to work with your actual data and formats to help resolve your issues. If your data is sensitve, then copy the file and substitute fake data in the one that is uploaded. If you have a really large file, you can delete all but a few rows in the uploaded copy. We just need enough to understand your data layout and arrangement of forms, tables, controls, etc.

My first advice, if you want to use VBA, is to get rid of the merged cells. While it can be done, the current design of your worksheets is not conducive to simple macros to do what you want. The problem is trying to tie the functions to the staff categories. The Hospital and Ward are pretty straight forward and can easily identify the sheet and row you want to put the data in, but the column would take a bit more doing becaue of the different staff categories for the same service. Like I said, it can be done, but I am not going to spend the time on it to try and do it. Perhaps someone else will be willing to try it now that they can see what you are working with. Thanks for providing the link to your file, it saves a lot of time and explaning.
Regards, JLG