The only way to do it dynamically would be to use an exact naming convention or structure in your worksheet. If the data is formatted as you posted, that can be done.

For the code, when working in MSOffice, the VBA macro recorder is your friend. When you paste, your cursor is at the top left. To get to the end, press Ctrl+End. That is what the command does for you.