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



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

[MRichmond]

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