Format Columnar Text File

[koolwaters]

Hi,

I have a text file where the pages are arranged vertically. Each page hold 15 records and then the next 15 records appear on the next page. The data essentially consists of four columns and the headings are Customer ID, Customer Name, Customer Type, Sequence No. The number of records vary and I need assistance in automating the import process.

When I use the text to columns wizard, I will get the first 15 records in columns A:D then the next 15 in E:H, I:L, etc.

What I really want is the data in columns E:H under the last record in columns A:D and the data in I:L under that, etc.

Just under the last record, the text 'Total Count' appears and the number of records, which in most instances are more than 1000 so it is tedious to cut and paste.

I hope I have been clear in my explanation.

Thanks.

 

[koolwaters]

I have been able to get through to some extent with the code but I am having an issue looping through the columns as this will be my first time working with loop statements. The below code "works" but only the data from the last 4 columns are cut and pasted. The test file I am working on has data across to columns HT.

Sub CutPasteColumns()
Dim myLastRow As Long, myLastColumn As Long
Dim i As Integer

myLastRow = Cells(Rows.Count, "A").End(xlUp).Row
myLastColumn = Cells(2, Columns.Count).End(xlToLeft).Column

i = myLastColumn
    Do Until IsEmpty(Cells(2, myLastColumn))
        Range(Cells(2, myLastColumn - 3), Cells(17, myLastColumn)).Cut Range("A" & myLastRow + 1)
        Application.CutCopyMode = False
        Debug.Print myLastColumn
    Loop
End Sub

This code below does not work and Excel hangs up each time I attempt to run the code.

Sub CutPasteColumns()
Dim myLastRow As Long, myLastColumn As Long
Dim i As Integer

myLastRow = Cells(Rows.Count, "A").End(xlUp).Row
myLastColumn = Cells(2, Columns.Count).End(xlToLeft).Column

i = myLastColumn - 4
    Do Until IsEmpty(Cells(2, i))
        Range(Cells(2, myLastColumn - 3), Cells(17, myLastColumn)).Cut Range("A" & myLastRow + 1)
        Application.CutCopyMode = False
        Debug.Print myLastColumn
    Loop
End Sub

I figured it would be easier to start with the last column and move to A.

Essentially, what I am trying to do is cut the data from HQ2:HT16 and paste it in the next blank cell in column A, then cut the data in HM2:HP16 and paste it in the next blank cell in column A, etc until E2:H16 as A2:D16 will contain the first 15 records in the data.

All I need is a way to loop from HT to E.

Thanks.

 

[koolwaters]

No one has any suggestions??????

 

[Fluff]

How about

Sub CutPasteColumns()
   Dim myLastColumn As Long
   Dim i As Integer
   
   myLastColumn = Cells(2, Columns.Count).End(xlToLeft).Column
   
   For i = 5 To myLastColumn Step 4
      Range(Cells(2, i), Cells(17, i + 3)).Cut Range("A" & Rows.Count).End(xlUp).Offset(1)
   Next i
   Application.CutCopyMode = False
End Sub

 

[koolwaters]

Many thanks.

That did the job.

 

[Fluff]

Glad to help & thanks for the feedback