Copy Multiple Destinations Using VBA?

I was wondering if there was a way to paste to multiple destinations using VBA within one copy destination argument. I should add that the cell I am using as a reference point is a variable that I created earlier in the process . The code I currently have is:

Code:
Sub find_last_row_of_data()
Dim rFoundCell As Range[code]...

Using the "Copy Destination" the way I have does the job and works fine. However there are about thirty other cells I have to add that the cell gets pasted to. Is there a better way to do this?

Secondly, and here is where I have no clue how to proceed, I need the finished sub to run automatically in the worksheet, so that each time the user adds a value in the next empty row in Column A, it will run the code. I should also add that the user actually enters the information on a different worksheet, the cells in Column A are linked to it using a formula. So there will be no actual physical imputing of data on this page, but the cell will still go from blank (with a formula) to a text string as it's value.