I want to transfer data in a list based on value to an other workb

M

Mully911

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
 
J

Joel

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
 
M

Mully911

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
 
J

Joel

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
 
M

Mully911

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
 
J

Joel

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.
 
M

Mully911

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!!!
 
J

Joel

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
 
M

Mully911

Hi Joel,
I messed up. Here is what I have:
It copies only part of column B to the other workbook and puts it in column L.
What am I missing??
thanks!
Mike

Sub MakeColumns()

Set OldSht = Workbooks("Book4").Sheets("Sheet1")
Set NewSht = ThisWorkbook.Sheets("Sheet1")


NewCol = 1
RowCount = 1
Start = RowCount
With OldSht
Do While .Range("B" & RowCount) <> ""
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

Loop

End With

End Sub
 
J

Joel

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.
 
M

Mully911

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
 
J

Joel

Set to Medium Security. Close Excel and Re-Open. Make sure you select
Enable Macros when you open the workbook.

Set Security from menu Tools - Macro - Security
 
M

Mully911

Hi Joel,

I have it running again, but it still has numbers less than 1.0--- say 0.121
at the top of some columns after it is complete??

Also I have many separate workbooks containing these data sets in the same
format and I wish to add each to the same "Master Sheet"

So if I add 10 sets of data from 1 book using the sweeet code you created ,I
would like to add the next 10 sets right after the prev set to build a huge
table.
Is this possible?

Thanks again!!!!
Mully
 
J

Joel

Remember to change the Workbook and Sheet name. I made the changes and found
the error in the code.


Sub MakeColumns()

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

LastCol = NewSht.Cells(1, Columns.Count).End(xlToLeft).Column
NewCol = LastCol + 1

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

.Range("B" & Start & ":B" & 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
Start = RowCount
End If
Loop

End With

End Sub
 
M

Mully911

Joel,
I really ,really appreciate you helping me with this!!

This is AWESOME!! It works good but still puts numbers less than zero at the
top of some columns where the list had more than 1 number less than 0 at the
end of the data?? I guess they could be negative numbers....??
TNX
Mully
 
J

Joel

Clear the destination worksheet and re-run the macro. I think the numbers
less the 1 was left over from a previous run of the macro.
 
M

Mully911

Huge thanks Joel!
This works GREAT!! Your the man....
Thanks for all your help on this!!

Mully
 
M

Mully911

Hi Joel,
I was wondering if you would help me with the next step in my number
crunching dilemma??

You helped me sort and transfer the data into a usable format, now I need
help to find the last number in each data list and record the corresponding
"runtime" which is located in column A of the spread sheet. Here is an
example:

A B C D
Time data1 data2 data3
0 16 15 17
2 15 14 16
4 13 14 16
6 13 13
8 12

I need a list that shows data1 ran 4min, data2 ran 8min and data3 ran6 min
and so on..
..
I have thousands of data sets to log. Arrrggggg!

Thanks in Advance!
Mully
 
Top