moving rows into columns
 

I have a spreadsheet where column a has the customer name, under column b I
have 8 cells with information that I move to columns.
Column a: Name
Column b: item 1
item 2
:
Item 8
and I want to display it as:
Column a: name, Column b: item1, column c: item 2...
Is there a way to do it without having to do it manually? Your assistance
is greatly appreciated.

moving rows into columns
 

The following code will do it.

Sub AAA()
Dim StartCell As Range
Dim N As Long
Dim DestCell As Range

'<< CHANGE StartCell to the first cell of data
'<< CHANGE DestCell to the first cell of the new data
Set StartCell = Worksheets("Sheet1").Range("A1")
Set DestCell = Worksheets("Sheet2").Range("A1")

Do Until False
DestCell = StartCell.Value
For N = 1 To 8
DestCell(1, N + 1) = StartCell(N, 2)
Next N
Set StartCell = StartCell(9, 1)
Set DestCell = DestCell(2, 1)
If StartCell(1, 2) = vbNullString Then
Exit Do
End If
Loop
End Sub

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)





On Fri, 28 Aug 2009 09:33:01 -0700, Jerry
wrote:

I have a spreadsheet where column a has the customer name, under column b I
have 8 cells with information that I move to columns.
Column a: Name
Column b: item 1
item 2
:
Item 8
and I want to display it as:
Column a: name, Column b: item1, column c: item 2...
Is there a way to do it without having to do it manually? Your assistance
is greatly appreciated.

moving rows into columns
 

Chip:
It help a lot, now the question is how can I pick instead of column b pick
column d?

"Chip Pearson" wrote:

The following code will do it.

Sub AAA()
Dim StartCell As Range
Dim N As Long
Dim DestCell As Range

'<< CHANGE StartCell to the first cell of data
'<< CHANGE DestCell to the first cell of the new data
Set StartCell = Worksheets("Sheet1").Range("A1")
Set DestCell = Worksheets("Sheet2").Range("A1")

Do Until False
DestCell = StartCell.Value
For N = 1 To 8
DestCell(1, N + 1) = StartCell(N, 2)
Next N
Set StartCell = StartCell(9, 1)
Set DestCell = DestCell(2, 1)
If StartCell(1, 2) = vbNullString Then
Exit Do
End If
Loop
End Sub

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)





On Fri, 28 Aug 2009 09:33:01 -0700, Jerry
wrote:

I have a spreadsheet where column a has the customer name, under column b I
have 8 cells with information that I move to columns.
Column a: Name
Column b: item 1
item 2
:
Item 8
and I want to display it as:
Column a: name, Column b: item1, column c: item 2...
Is there a way to do it without having to do it manually? Your assistance
is greatly appreciated.

moving rows into columns
 

Chip:
Never mind, I just found the cell. Thanks a lot

"Chip Pearson" wrote:

The following code will do it.

Sub AAA()
Dim StartCell As Range
Dim N As Long
Dim DestCell As Range

'<< CHANGE StartCell to the first cell of data
'<< CHANGE DestCell to the first cell of the new data
Set StartCell = Worksheets("Sheet1").Range("A1")
Set DestCell = Worksheets("Sheet2").Range("A1")

Do Until False
DestCell = StartCell.Value
For N = 1 To 8
DestCell(1, N + 1) = StartCell(N, 2)
Next N
Set StartCell = StartCell(9, 1)
Set DestCell = DestCell(2, 1)
If StartCell(1, 2) = vbNullString Then
Exit Do
End If
Loop
End Sub

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)





On Fri, 28 Aug 2009 09:33:01 -0700, Jerry
wrote:

I have a spreadsheet where column a has the customer name, under column b I
have 8 cells with information that I move to columns.
Column a: Name
Column b: item 1
item 2
:
Item 8
and I want to display it as:
Column a: name, Column b: item1, column c: item 2...
Is there a way to do it without having to do it manually? Your assistance
is greatly appreciated.