StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Start recording a macro and open the txt file delimited by space. press ctrl + f and find First, Second and Third in three steps. copy your required range from the active workbook to your required workbook in respective sheets. </p> <p>this will give you a recorded macro which you can restructure to make an automated code. Maybe i can post some code later. you will have to add lines of vba to read the line numbers of the cells used to find secon and third so that you know what range you have to copy.</p> <p>1 question, number of columns in your file is as shown????</p> <p>here is a code.</p> <pre><code>Sub Macro1() Dim startRow As Integer Dim endRow As Integer Dim wb As Workbook Workbooks.OpenText Filename:="D:\file.txt", Origin:=437, startRow:=1, _ DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter _ :=True, Tab:=True, Semicolon:=False, Comma:=False, Space:=True, Other _ :=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1)), _ TrailingMinusNumbers:=True Set wb = ActiveWorkbook Cells.Find(What:="FIRST", After:=ActiveCell, LookIn:=xlFormulas, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False, SearchFormat:=False).Activate startRow = ActiveCell.Row + 1 Cells.Find(What:="SECOND", After:=ActiveCell, LookIn:=xlFormulas, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False, SearchFormat:=False).Activate endRow = ActiveCell.Row - 1 Range("A" & startRow & ":D" & endRow).Copy ThisWorkbook.Activate Sheets("Sheet1").Select Range("A1").Select ActiveSheet.Paste startRow = endRow + 2 wb.Activate Cells.Find(What:="THIRD", After:=ActiveCell, LookIn:=xlFormulas, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False, SearchFormat:=False).Activate endRow = ActiveCell.Row - 1 Range("A" & startRow & ":D" & endRow).Copy ThisWorkbook.Activate Sheets("Sheet2").Select Range("A1").Select ActiveSheet.Paste wb.Activate startRow = endRow + 2 endRow = Range("A" & Rows.Count).End(xlUp).Row Range("A" & startRow & ":D" & endRow).Copy ThisWorkbook.Activate Sheets("Sheet3").Select Range("A1").Select ActiveSheet.Paste wb.Close (False) End Sub </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload