Moving Data

[yanks6rule]

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]

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

 

[Ken Johnson]

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

 

[Ken Johnson]

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

 

[Dave Peterson]

Darn arithmetic!

Thanks for the correction.

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

 

[Ken Johnson]

You're welcome Dave.
Ken Johnson

 

[yanks6rule]

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

 

[yanks6rule]

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]

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

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]

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

 

[yanks6rule]

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

 

[yanks6rule]

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