Hi all

I'm a bit rusty at VB - so forgive basic errors please. I've searched and
searched online help and google groups, but to no avail. I think my
programming knowledge may be a bit basic for what I'm trying to do.

Full details below - the question I have is this: how do I get the cells to
copy down, and then to get the cut'n'paste to work so I can parse the data
again? Driving me mad and I've spent nearly all day today on it... :o(

Kind regards

Nick
(email address in headers, as cunningly disguised as I can muster at this
time of day)

ps - any help on "streamlining" my appalling code very much welcomed.
--------------------------------------------------------------------------

Our accounts system has been set to only output data as fixed width text
files (can't get it changed - not for discussion). Example is below:

621660 A COMPANY LTD
LONDON 06937829 22,289.48 Yes
10 140270603 30-JUL-03 ROY0603
231.78 0.00 231.78
7920 AP
Invoice 158307 30-JUL-03
20 128690603 05-AUG-03 ROY0603
161.18 0.00 161.18
7920 AP
Invoice 158971 05-AUG-03
30 130730603 27-AUG-03 ROY0603
102.98 0.00 102.98
7920 AP
Invoice 161509 27-AUG-03
40 150200603 10-SEP-03 ROY0603
164.64 0.00 164.64
7920 AP
Invoice 163881 10-SEP-03
50 151590603 10-SEP-03 ROY0603
171.38 0.00 171.38
7920 AP
Invoice 163882 10-SEP-03
60 144670603 17-SEP-03 ROY0603
21,457.52 0.00 21,457.52
7920 AP
Invoice 165187 17-SEP-03

I've tried to write a macro to take each line and move it to the top of the
sheet (or next available line) and parse it. I've done it in 3 steps to
reflect the three different lines of data that's there.

Step 1:
-----------------
Sub AP_Line1()
'
' AP_Line1 Macro
' Macro recorded 11/02/2004 by Nick Read
'
' Move data to topmost row

Selection.Cut
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste
' Convert to columns
Selection.TextToColumns Destination:=ActiveCell, DataType:=xlFixedWidth,
_
FieldInfo:=Array(Array(0, 9), Array(9, 2), Array(15, 9), Array(50,
2), Array(74, 2), _
Array(91, 2), Array(99, 1), Array(124, 9))
' Move the cursor to the next row, first cell ready for part 2
Selection.End(xlToLeft).Select
Selection.End(xlDown).Select

End Sub
--------------------
works fine.

Step 2:
----------------------
Sub AP_line2()
'
' AP_line2 Macro
' Macro recorded 11/02/2004 by Nick Read
'
'
Selection.Cut
Selection.End(xlUp).Select
Selection.End(xlToRight).Select
ActiveCell.Offset(0, 1).Select
ActiveSheet.Paste
Selection.TextToColumns Destination:=ActiveCell, DataType:=xlFixedWidth,
_
FieldInfo:=Array(Array(0, 9), Array(25, 2), Array(34, 9), Array(39,
4), Array(48, 2), _
Array(60, 1), Array(86, 1), Array(105, 1), Array(124, 9))

' Move the cursor to the next row, first cell ready for part 3

Selection.End(xlToLeft).Select
Selection.End(xlDown).Select
End Sub
--------------------
Also works fine.

Step 3 not really necessary to show here is to do the same with the third
line.

Problem comes when I try to copy the first line to the row beneath it, and
then go back to the next row of unparsed data and move and parse it.
viz. step 2a
------------------
Sub AP_line2a()
'
' AP_line2a Macro
' Macro recorded 11/02/2004 by Nick Read
'
'
Selection.End(xlUp).Select
Selection.Resize(Selection.Rows.Count + 0, Selection.Columns.Count +
5).Select
Selection.Copy
Selection.End(xlToLeft).Select
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste
Selection.End(xlToLeft).Select
Selection.End(xlDown).Select
' works fine to this point
' this next bit doesn't work - error is "No data to parse"
ActiveCell.Cut

Selection.End(xlUp).Select
Selection.End(xlToRight).Select
ActiveCell.Offset(0, 1).Select

Selection.TextToColumns Destination:=ActiveCell, DataType:=xlFixedWidth,
_
FieldInfo:=Array(Array(0, 9), Array(25, 2), Array(34, 9), Array(39,
4), Array(48, 2), _
Array(60, 1), Array(86, 1), Array(105, 1), Array(124, 9))

' Move the cursor to the next row, first cell ready for part 3

'Selection.End(xlToLeft).Select
'Selection.End(xlDown).Select
End Sub
--------------------------------