Macro Help
 

Hello all
I am trying to import a text file into my workbook. The text file is 10 rows
by 13 colums. I need to convert this file from row wise to columnwise with a
twist. I need the first 3 cells of each row (from the text file)
concatenated to create a header for each column. The headed should be
delimited by "-"
Example:
Text file
Row 1 a b c 1 2 3 4 5 6 7 8 9 10
Row2 d e f 5 6 2 6 7 8 3 4 2 11

Imported it would be would be

a-b-c d-e-f
1 5
2 6
3 2
4 6
5 7
6 8
7 3
8 4
9 2
10 11

Thanks in advance!
BOB

Macro Help
 

Option Explicit
Sub testme()
Dim curWks As Worksheet
Dim newWks As Worksheet
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim oCol As Long
Dim rngToCopy As Range

Set curWks = Worksheets("sheet1")
Set newWks = Worksheets.Add

With curWks
FirstRow = 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

oCol = 0
For iRow = FirstRow To LastRow
oCol = oCol + 1
newWks.Cells(1, oCol).Value _
= .Cells(iRow, "A").Value & "-" _
& .Cells(iRow, "B").Value & "-" _
& .Cells(iRow, "C").Value
Set rngToCopy = .Range(.Cells(iRow, "D"), _
.Cells(iRow, .Columns.Count).End(xlToLeft))
rngToCopy.Copy
newWks.Cells(2, oCol).PasteSpecial Transpose:=True
Next iRow
End With

newWks.UsedRange.Columns.AutoFit

Application.CutCopyMode = False

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Bob wrote:

Hello all
I am trying to import a text file into my workbook. The text file is 10 rows
by 13 colums. I need to convert this file from row wise to columnwise with a
twist. I need the first 3 cells of each row (from the text file)
concatenated to create a header for each column. The headed should be
delimited by "-"
Example:
Text file
Row 1 a b c 1 2 3 4 5 6 7 8 9 10
Row2 d e f 5 6 2 6 7 8 3 4 2 11

Imported it would be would be

a-b-c d-e-f
1 5
2 6
3 2
4 6
5 7
6 8
7 3
8 4
9 2
10 11

Thanks in advance!
BOB

--

Dave Peterson

Macro Help
 

Thanks for the code and the link. Im trying to learn as I go and this NG is
a great help. This looks at sheet one in the current book. I need to import
from the text file (myfile.for)
Thanks!

"Dave Peterson" wrote:

Option Explicit
Sub testme()
Dim curWks As Worksheet
Dim newWks As Worksheet
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim oCol As Long
Dim rngToCopy As Range

Set curWks = Worksheets("sheet1")
Set newWks = Worksheets.Add

With curWks
FirstRow = 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

oCol = 0
For iRow = FirstRow To LastRow
oCol = oCol + 1
newWks.Cells(1, oCol).Value _
= .Cells(iRow, "A").Value & "-" _
& .Cells(iRow, "B").Value & "-" _
& .Cells(iRow, "C").Value
Set rngToCopy = .Range(.Cells(iRow, "D"), _
.Cells(iRow, .Columns.Count).End(xlToLeft))
rngToCopy.Copy
newWks.Cells(2, oCol).PasteSpecial Transpose:=True
Next iRow
End With

newWks.UsedRange.Columns.AutoFit

Application.CutCopyMode = False

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Bob wrote:

Hello all
I am trying to import a text file into my workbook. The text file is 10 rows
by 13 colums. I need to convert this file from row wise to columnwise with a
twist. I need the first 3 cells of each row (from the text file)
concatenated to create a header for each column. The headed should be
delimited by "-"
Example:
Text file
Row 1 a b c 1 2 3 4 5 6 7 8 9 10
Row2 d e f 5 6 2 6 7 8 3 4 2 11

Imported it would be would be

a-b-c d-e-f
1 5
2 6
3 2
4 6
5 7
6 8
7 3
8 4
9 2
10 11

Thanks in advance!
BOB

--

Dave Peterson

Macro Help
 

Record a macro that opens myfile.for.

Then add that to the top of the routine.

But use
set curwks = activesheet
(to point at the worksheet created when you import the file)

Bob wrote:

Thanks for the code and the link. Im trying to learn as I go and this NG is
a great help. This looks at sheet one in the current book. I need to import
from the text file (myfile.for)
Thanks!

"Dave Peterson" wrote:

Option Explicit
Sub testme()
Dim curWks As Worksheet
Dim newWks As Worksheet
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim oCol As Long
Dim rngToCopy As Range

Set curWks = Worksheets("sheet1")
Set newWks = Worksheets.Add

With curWks
FirstRow = 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

oCol = 0
For iRow = FirstRow To LastRow
oCol = oCol + 1
newWks.Cells(1, oCol).Value _
= .Cells(iRow, "A").Value & "-" _
& .Cells(iRow, "B").Value & "-" _
& .Cells(iRow, "C").Value
Set rngToCopy = .Range(.Cells(iRow, "D"), _
.Cells(iRow, .Columns.Count).End(xlToLeft))
rngToCopy.Copy
newWks.Cells(2, oCol).PasteSpecial Transpose:=True
Next iRow
End With

newWks.UsedRange.Columns.AutoFit

Application.CutCopyMode = False

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Bob wrote:

Hello all
I am trying to import a text file into my workbook. The text file is 10 rows
by 13 colums. I need to convert this file from row wise to columnwise with a
twist. I need the first 3 cells of each row (from the text file)
concatenated to create a header for each column. The headed should be
delimited by "-"
Example:
Text file
Row 1 a b c 1 2 3 4 5 6 7 8 9 10
Row2 d e f 5 6 2 6 7 8 3 4 2 11

Imported it would be would be

a-b-c d-e-f
1 5
2 6
3 2
4 6
5 7
6 8
7 3
8 4
9 2
10 11

Thanks in advance!
BOB

--

Dave Peterson


--

Dave Peterson