Copy data from textfile, paste and transpose to sheet2
 

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

Copy data from textfile, paste and transpose to sheet2
 

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).Past eSpecial _
Paste:=xlPasteAll, Transpose:=True
Next myRow

End Sub

HTH,
Bernie
MS Excel MVP

"m4nd4li4" wrote in message
om...
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