How to get text from wrapped text show on different columns using VBA

Question

I have a vba script that copies and paste data from one workbook to the other. The data that is being pasted over are wrapped text. I need my vba script to take the wrapped text and turn it to what you see below.

Below is the data that is being copied that has the wrapped text.

Below is what I want it to look like when pasting to the new workbook.

Below is my script of what I have right now.

Sub Get_Data_From_File()
Dim FileToOpen As Variant
Dim OpenBook As Workbook
    FileToOpen = Application.GetOpenFilename(Title:="Browse for your File & Import Range", FileFilter:="Excel Files (*.xls*),*xls*")
    Application.ScreenUpdating = False
    Cells.WrapText = False
    If FileToOpen <> False Then
        Set OpenBook = Application.Workbooks.Open(FileToOpen)
        OpenBook.Worksheets("MyFile").Range("A1:A352:I1:I352").copy _

        Workbooks("Book1").Worksheets("Sheet1").Range("A1:A352:I1:I352").PasteSpecial xlPasteValues
        'OpenBook.Close False
    End If
    Application.ScreenUpdating = True
End Sub

Answer 1

Perhaps this can get you going...

Sub alphanumEric()
    Dim i&, s, v
    
    s = OpenBook.Worksheets("MyFile").Range("D2").Value2
    s = Split(s, vbLf)
    ReDim v(0 To UBound(s), 1 To 1)
    
    For i = 0 To UBound(s)
        v(i, 1) = s(i)
    Next
    Workbooks("Book1").Worksheets("Sheet1").Range("D2").Resize(UBound(s) + 1).Value2 = v
    
End Sub