You can make the range dynamic by using something like this:
It will find the last populated row in column A and copy the selected cell content only down to that row. It could be based off a different column but I've used A in this example for simplicity.
There are also other ways of accomplishing the same and which you use will be down to personal preference and/or the process in hand.
Is the Excel file you attached what your data looks like before or after running your code? Reason I ask is it's obviously a recorded macro and therefore terrible as far as VBA goes. I was going to rewrite it for you to make it a bit easier to manage in future but if the attached isn't the data pre processing then I'd need to see what that looks like.
BSB
Bookmarks