Moving Data
 

The way I have a macro setup right now I can import multiple text files
into one worksheet. THe problem is it stacks it on top of one another
going down two columns. I want to make a second macro that will take
these two columns and break it into multiple columns so that each file
has its own unique two columns to do analysis on.

The code I tried writing was simple and was

Range("A177:B352").Select
Selection.Cut
Range("C1").Select
Selection.Paste
Range("A353:B528").Select
Selection.Cut
Range("E1").Select
Selection.Paste
Range("A529:B704").Select
Selection.Cut
Range("G1").Select
Selection.Paste
Range("A705:B880").Select
Selection.Cut
Range("I1").Select

and so on and so forth to move 14 different files from two columns to
28 columns. I get an error message that says Object doesn't support
this property or method. Is there any easier way of doing this or am I
missing something in my code.

Thanks
Bob

Moving Data
 

Soe each text file is exactly 175 lines long?

If yes, you can try:

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim oCol As Long
Dim iRow As Long
Dim myStep As Long

Set wks = Worksheets("sheet1")

myStep = 175
oCol = 1
With wks
For iRow = 177 To .Cells(.Rows.Count, "A").End(xlUp).Row Step myStep
oCol = oCol + 2
.Cells(iRow, "A").Resize(myStep, 2).Cut _
Destination:=.Cells(1, oCol)
Next iRow
End With

End Sub

yanks6rule wrote:

The way I have a macro setup right now I can import multiple text files
into one worksheet. THe problem is it stacks it on top of one another
going down two columns. I want to make a second macro that will take
these two columns and break it into multiple columns so that each file
has its own unique two columns to do analysis on.

The code I tried writing was simple and was

Range("A177:B352").Select
Selection.Cut
Range("C1").Select
Selection.Paste
Range("A353:B528").Select
Selection.Cut
Range("E1").Select
Selection.Paste
Range("A529:B704").Select
Selection.Cut
Range("G1").Select
Selection.Paste
Range("A705:B880").Select
Selection.Cut
Range("I1").Select

and so on and so forth to move 14 different files from two columns to
28 columns. I get an error message that says Object doesn't support
this property or method. Is there any easier way of doing this or am I
missing something in my code.

Thanks
Bob

--

Dave Peterson

Moving Data
 

Hi Bob,
judging by the numbers in your code, each file is 176 lines long (352
-177 + 1 = 176), so in Dave's code just change "myStep = 175" "myStep =
176" and you should get the correct result if all files have the same
number of lines.
Ken Johnson

Moving Data
 

That should have read "myStep = 175" to "myStep = 176".
Ken Johnson

Moving Data
 

Darn arithmetic!

Thanks for the correction.

Ken Johnson wrote:

That should have read "myStep = 175" to "myStep = 176".
Ken Johnson

--

Dave Peterson

Moving Data
 

You're welcome Dave.
Ken Johnson

Moving Data
 

So do I just change the myStep to 176 or do I have to put in "myStep =
175" to "myStep = 176"

Moving Data
 

So do I just change the myStep to 176 or do I have to put in "myStep =
175" to "myStep = 176".

Thanks for all the help guys I am going to copy this and try it now

Moving Data
 

Just change this one line:
from:
myStep = 175
to

yanks6rule wrote:

So do I just change the myStep to 176 or do I have to put in "myStep =
175" to "myStep = 176".

Thanks for all the help guys I am going to copy this and try it now

--

Dave Peterson

Moving Data
 

Just change this one line
from:
myStep = 175
to:
myStep = 176



yanks6rule wrote:

So do I just change the myStep to 176 or do I have to put in "myStep =
175" to "myStep = 176".

Thanks for all the help guys I am going to copy this and try it now

--

Dave Peterson

Moving Data
 

Dave,

This works great except for one flaw, it cuts and pastes the second
dataset correctly but it puts it in C2 instead of C1 but then in the
third dataset it goes back to E1 but it start with the last data point
of the previous set so I get point 176 at the top instead of point one.

Does this make sense and if so what does that mean?

Thanks for all your help
Bob

Moving Data
 

It worked....thanks everyone for their help, this will shave hours off
of my data analysis.