VBA Extracting Specific Data from a Text File into Multiple Worksheets

[csjung]

Greetings,
I'm a self-taught excel user who is lucky enough to peruse this forum. I have been able to piecemeal what I need for the most part for various projects but have come short on my current task.

I am seeking help with code that will:
Copy and Paste selected data from all .txt files in constant folder to specified worksheets and cells:

• the Last Number from rows 1-5 = to last empty cell in row 5-9 of workbook
• the Last Number from rows 6-8 = to last empty cell in row 10-12 of workbook
• the Last Number from rows 9-11 = to last empty cell in row 13-15 of workbook

ex. All .txt files in folder will look like this: sheet1.txt

If interested, this is what I need the code for in my project, any help for the whole process is greatly appreciated :

1. Loop through all .txt files from folder constant to active workbook Const conSpath As String = "C:\\data\"
2. If ActiveSheet.Name="PA" Then selection = conSpath & "sheet1.txt" etc.
3. Select & Copy the select data from .txt file
4. Paste selection to next empty cell in row 5 of workbook Range("IV5").End(xlToLeft).Offset(0, 1).Select
   See workbook for clarification, see Grey cells in PA sheet: sheet1.xlsx

Please let me know if this is possible, if not, I can rework the process.