Copying a range in a macro which can vary in length every time I use the macro

I am trying to copy a range in a macro which is in the same 1 column but the number of rows can vary each I am running the macro.

When recording the macro, if I use Ctrl Shift and Down Arrow to highlight the range to the last cell with a value, it will assign the last cell reference in the range of that particular spreadsheet. If I use the macro in another spreadsheet which has a longer range, it will only take the range assigned in the original spreadsheet because the last cell reference has been"hardcoded" in the macro.

Any ideas?