Copy specified data from notepad to excel

Originally Posted by dOOb

Let me know if this works:

Sub NewSub()

    Dim FSO As Object
    Dim Fld_obj As Object
    Dim File_obj As Object
    Dim FilePath As String
    Dim Hdr As String
    Dim myStr As String
    Dim myTxtFile
    Dim Txt_arr
    Dim i As Long
    Dim r As Long
    Dim c As Long

    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set Fld_obj = FSO.GetFolder("C:\Text Files") 'update to your own folder
    
    With ActiveSheet
        For c = 2 To .UsedRange.Columns.Count 'define your end value differently if needed
            Hdr = .Cells(1, c).Value
            
            For r = 2 To .UsedRange.Rows.Count 'as above
                Filename = .Cells(r, 1).Value
                If FSO.FileExists(Fld_obj & "\" & Filename) Then
                    FilePath = Fld_obj & "\" & Filename
                    
                    Set myTxtFile = FSO.OpenTextFile(FilePath, ForReading) 'this reads the file to "memory"
                    Txt_arr = Split(myTxtFile.ReadAll, vbNewLine) 'this splits the text into array
                    
                    For i = 0 To UBound(Txt_arr) 'loop through text, find line matching the header
                        If Txt_arr(i) = Hdr Then
                            Do Until Txt_arr(i) Like "*------*" 'iterate until the "-----" line is found
                                If myStr = vbNullString Then
                                    myStr = Txt_arr(i) & vbNewLine
                                Else
                                    myStr = myStr & Txt_arr(i) & vbNewLine
                                End If
                                i = i + 1
                            Loop
                            .Cells(r, c).Value = myStr 'populate data in worksheet
                            myStr = vbNullString
                            Exit For
                        End If
                    Next i
                End If
            Next r
        Next c
    End With
    
End Sub

Thanks for your effort dOOb! But unfortunately this isn't working as expected.

as requested earlier, please find the attached sample sheet.