Solved: Text to Columns or something similar

[MRichmond]

Good afternoon all,

Here's hoping someone can help.

I have a workbook, and on sheet "Summary" people enter stock in column H. I need to take whatever is entered in (notice the break in sample workbook) it will always be qty and stock no, and break the data down into two columns on the "Data" worksheet.

I know I can do this with text to columns, and then moving it manually, but the Summary could contain 200+ rows, so I'm looking for some VBA code to automate

Any ideas?

[Bob Phillips]

Just add a simple loop after the text to columns to move the pairs to rows

[vba]
Function MoveValuePairs()
Dim Lastcol As Long
Dim NextRow As Long
Dim i As Long

Application.ScreenUpdating = False

With Worksheets("Data")

Lastcol = .Range("A1").End(xlToRight).Column
NextRow = 1
For i = 1 To Lastcol Step 2

NextRow = NextRow + 1
.Cells(1, i).Resize(, 2).Copy .Cells(NextRow, "A")
Next i

.Range("A1:B1").Value2 = Array("Qty", "Stock No")
.Cells(1, "C").Resize(, Lastcol).ClearContents
End With

Application.ScreenUpdating = True

End Function[/vba]

[MRichmond]

Thanks XLD, that's just what I pictured I needed.