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.

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.

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.

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.