Copy data from textfile, paste and transpose to sheet2

[m4nd4li4]

Hi,

I have a collection of text files which contains the following.....

Height1,0.00096
Height2,0.00771
Height3,-0.00672
Height4,0.01426
Height5,-0.23803
Angle1,22.94082
Angle2,21.43342
Angle3,21.67265
Angle4,21.43239
Angle5,21.22298


Height1,-0.02246
Height2,-0.00283
Height3,0.00787
Height4,0.01151
Height5,-0.27337
Angle1,20.93959
Angle2,22.23021
Angle3,20.86581
Angle4,21.57723
Angle5,18.08423


Height1,-0.03233
Height2,0.00005
Height3,-0.00847
Height4,-0.00339
Height5,-0.2792
Angle1,21.69544
Angle2,21.24632
Angle3,21.26402
Angle4,21.30992
Angle5,21.27491

The text file can contain 100's of these types of results. I can
import the data from the textfile to excel using the text import
wizard. So now I have 2 columns of data....


Height1 0.00096
Height2 0.00771
Height3 -0.00672
Height4 0.01426
Height5 -0.23803
Angle1 22.94082
Angle2 21.43342
Angle3 21.67265
Angle4 21.43239
Angle5 21.22298


Height1 -0.02246
Height2 -0.00283
Height3 0.00787
Height4 0.01151
Height5 -0.27337
Angle1 20.93959
Angle2 22.23021
Angle3 20.86581
Angle4 21.57723
Angle5 18.08423


Height1 -0.03233
Height2 0.00005
Height3 -0.00847
Height4 -0.00339
Height5 -0.2792
Angle1 21.69544
Angle2 21.24632
Angle3 21.26402
Angle4 21.30992
Angle5 21.27491


What I would like to do is to copy the numerical data, transpose and
paste on a second sheet (sheet2), like this....

Height1 Height2 Height3 Height4 Height5 Angle1 Angle2 Angle3 Angle4 Angle5

0.00096 0.00771 -0.00672 0.01426 -0.23803 22.94082 21.43342 21.67265 21.43239 21.22298
-0.02246 -0.00283 0.00787 0.01151 -0.27337 20.93959 22.23021 20.86581 21.57723 18.08423
..
..
..
etc
etc
..
..


I recorded a macro to do this....

Sub Macro1()
'
Range("B2:B11").Select
Selection.Copy
Sheets("Sheet2").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True
Sheets("Sheet1").Select
Range("B14:B23").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Range("A3").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True
Sheets("Sheet1").Select
Range("B26:B35").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Range("A4").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True
End Sub

I'm not too sure how you would "automate" this for ALL the data in the
text file. If its any help, I could always send the textfile.

Regards,

Bharesh

 

[Bernie Deitrick]

Bharesh,

As long as your data sets are always 10 rows long, and are separated by 2
blank rows, and start in row 2:

Sub Macro1()
Dim myRow As Long

Sheets("Sheet1").Select

For myRow = 2 To Range("B65536").End(xlUp).Row Step 12
Range("B" & myRow).Resize(10, 1).Copy
Sheets("Sheet2").Range("A65536").End(xlUp)(2).PasteSpecial _
Paste:=xlPasteAll, Transpose:=True
Next myRow

End Sub

HTH,
Bernie
MS Excel MVP