Worksheet Merge Excel Mac 2004

[Gene Augustin]

MAC Powerbook G4, OS 10.5.6 Excel 2004

I have a worksheet with many rows having 8 columns of data. Each column has
a header name (First cell in column is name for column data). All data is
one contiguous region.

I want to macro to a new worksheet with 1 column of data with the 8 columns
(A:H) of the original data transposed into the new sheet column A. If I have
50 rows of 8 columns to start, I will end up with 1 column with 400 rows.

For example


D04/04/2008 T-410.00 CX N1001 PAchillean M03-29-20 L1099 ^
D04/11/2008 T-490.00 CX N1004 PAchillean M4-5-20 L1099 ^

Becomes


D04/04/2008
T-410.00
CX
N1001
PAchillean
M03-29-20
L1099
^
D04/11/2008
T-490.00
CX
N1004
PAchillean
M4-5-20
L1099
^

 

[Joel]

Sub splitrows()

Set oldsht = ActiveSheet
Set newsht = Sheets.Add(after:=Sheets(Sheets.Count))


OldRowCount = 1
NewRowCount = 1
Do While oldsht.Range("A" & OldRowCount) <> ""
ColData = oldsht.Range("A" & OldRowCount & ":H" & OldRowCount)
For Each cell In ColData
newsht.Range("A" & NewRowCount) = cell
NewRowCount = NewRowCount + 1
Next cell

OldRowCount = OldRowCount + 1
Loop


End Sub