PDA

View Full Version : Solved: Text to Columns or something similar



MRichmond
01-27-2012, 07:21 AM
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
01-27-2012, 10:03 AM
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
01-30-2012, 12:14 AM
Thanks XLD, that's just what I pictured I needed.