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

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

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

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

Hi Joel,
I was able to complete the first 3 instructions below but don't get what you
are saying after that. Can you make it simpler for me please?
Thank you
Mully

"Joel" wrote:

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

4) Replace the line
from
Set NewSht = Workbooks("Book1").Sheets("Sheet4")
to
Set NewSht = Thisworkbook.Sheets("Sheet1")

This will put the data into the workbook wherre the macro is located.


Change line below to match the workbook and sheet where your source data is
located.

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

You said you had two workbooks. One where the data was located and a 2nd
where you wanted to put the data. For example change the location where the
data is lcoated. "DATA" is the string on the TAB at the bottom of the
worksheet.

Set OldSht = Workbooks("MyBook.Xls").Sheets("Data")

5) to simplify running the code just press F5 when you have the VBA windcow
open.

"Mully911" wrote:

Hi Joel,
I was able to complete the first 3 instructions below but don't get what you
are saying after that. Can you make it simpler for me please?
Thank you
Mully

"Joel" wrote:

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

Hi Joel,

Wow this is GREAT!

I did get it to copy the list over to columns of data, but I forgot to tell
you I only require the data in column B to be transferred.
Also there may be more than 1 number at the end of the data set that is less
than 1.0. Will you fine tune it for me?? please!
Thanks s!!!

"Joel" wrote:

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

You still have to change the same two lines you did before. I added code to
skip all the numbers less than 1 and changed the data that was moving from
Column A to Column B.


Sub MakeColumns()

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

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

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

End With

End Sub


"Mully911" wrote:

Hi Joel,

Wow this is GREAT!

I did get it to copy the list over to columns of data, but I forgot to tell
you I only require the data in column B to be transferred.
Also there may be more than 1 number at the end of the data set that is less
than 1.0. Will you fine tune it for me?? please!
Thanks s!!!

"Joel" wrote:

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

I missed 1 change from A to B

from:
..Range("B" & Start & ":A" & RowCount).Copy _
to
..Range("B" & Start & ":B" & RowCount).Copy _

The code is copying both A and B and I think the A data is clearing out th B
data that was transfered. It is impossible for the code to write to column L
without first writing to columns B - K.


"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

Hi Joel,
I closed Excel and now I get "security is too high or unsigned" I tried to
lower security but nothing happens??
Any thoughts??
Thanks!
Mully

"Joel" wrote:

I missed 1 change from A to B

from:
.Range("B" & Start & ":A" & RowCount).Copy _
to
.Range("B" & Start & ":B" & RowCount).Copy _

The code is copying both A and B and I think the A data is clearing out th B
data that was transfered. It is impossible for the code to write to column L
without first writing to columns B - K.


"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