problem with pasting data from notepad file

Before I tried this code I have been manually pasting it in excel by highlighting all of column A and pasting. I then used text to columns in the data tab with delimeter > tab and comma. This works but I have to do this 36 times for every report and there's 20 reports so that's why I wanted to automate the whole process with the press of a button.

When I do this with the code, it doesn't.

Forgot to mention I only have to take out a column of numbers from this file. I can't just highlight the 1st number and use shift and end to go to the last number. I have to delete a row after a certain number of rows about 3 times.