The code assumes the two workbook are opend. The best way of doing this is
to put the macro in the workbook where you are putting the data.

1) From Destination workbook type Alt-F11 to get to VBA window
2) Insert a module from VBA menu Insert - Module
3) Paste code from posting on module page in VBA.
4) Change these two lines as required. Thisworkbook is the workbook where
the macro is located. I changed the sheet name from sheet4 to Sheet1. Do
the same for OldSht (the workbook where the data is in one column) changing
the workbook name and sheet as required.

from
Set OldSht = Workbooks("Book1").Sheets("Sheet3")
Set NewSht = Workbooks("Book1").Sheets("Sheet4")

to
Set OldSht = Workbooks("Book1.xls").Sheets("Sheet3")
Set NewSht = Thisworkbook.Sheets("Sheet1")

5) You can run the code a few diffferent ways.

a) From the VBA window type F5
b) From VBA window Run - run Sub/Userform
c) From worksheet Tools - Macro - Macro - Make Columns

6) the code does the same thing as is you manuall copied and pasted the data
one section at a time. It copies from the 1st row to the row before the
number less than one. Then pastes the data into the new workbook. Then it
skips the number less than one and then repeats the copy and paste.

Note : The code assumes all the numbers are POSITIVE. Negaitve numbers are
less than one.

Sub MakeColumns()

Set OldSht = Workbooks("Book1").Sheets("Sheet3")
Set NewSht = Workbooks("Book1").Sheets("Sheet4")

NewCol = 1
RowCount = 1
Start = RowCount
With OldSht
Do While .Range("A" & RowCount) < ""
If .Range("A" & (RowCount + 1)) < 1 Or _
.Range("A" & (RowCount + 1)) = "" Then

.Range("A" & Start & ":A" & RowCount).Copy _
Destination:=NewSht.Cells(1, NewCol)
NewCol = NewCol + 1
RowCount = RowCount + 2
Start = RowCount
Else
RowCount = RowCount + 1
End If

Loop

End With

End Sub


"Mully911" wrote:


Hi Joel,

Thanks for you awesome reply!
I am not sure how to use this though, please explain if you would. I have
only made simple formula's in Excel. Im a true novice.

I could really use a step by step of what this does and how it works.
You are about to save me weeks of sorting through data.
I really appreciate your help!!!
Thanks again,
Mully

"Joel" wrote:

Sub MakeColumns()

Set OldSht = Workbooks("Book1").Sheets("Sheet3")
Set NewSht = Workbooks("Book1").Sheets("Sheet4")

NewCol = 1
RowCount = 1
Start = RowCount
With OldSht
Do While .Range("A" & RowCount) < ""
If .Range("A" & (RowCount + 1)) < 1 Or _
.Range("A" & (RowCount + 1)) = "" Then

.Range("A" & Start & ":A" & RowCount).Copy _
Destination:=NewSht.Cells(1, NewCol)
NewCol = NewCol + 1
RowCount = RowCount + 2
Start = RowCount
Else
RowCount = RowCount + 1
End If

Loop

End With

End Sub


"Mully911" wrote:

I have recorded number data in a large list. The data contains individual
recordings seperated by data values less than 1.
In other words each set of data(each recording) is seperated by a value less
than 1.

Example:
18
17
16
.03
19
18
16
.05
I want to copy each data set to another work book and place it in the next
column one after the other.

Here is how I would like the data in the second workbook to look
18 19
17 18
16 16
I could really use some help with this I am new to programming.

Thanks in advance
Mully