is there any way to create a macro that can open a file and then count
the number of rows within that file (a csv) insert that amount of rows
into an existing spreadsheet (where cell is selected) and then copy the
contents into those new rows?

one of the problems is that it is not a specific file, so is there a
way to have the open file window appear aswell?