Martin,

Sorry for being so dense... Patience is a virtue, I guess ;-)

Anyway, try the macro below. I wasn't sure what you wanted to do with columns L to AE after all
this is done...so I deleted them. Take out the line

Range("L:AE").EntireColumn.Delete

if you don't want them deleted.

Also, I'm also assuming that you data is all constants, not formulas or links.....

HTH,
Bernie
MS Excel MVP


Sub MartinDataRearrange()
Dim myA As Range
Dim myR As Range
Dim mySel As String
Dim myRow As Long
Dim i As Long

mySel = Selection.Address

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

myRow = Cells(Rows.Count, 1).End(xlUp).Row

For i = myRow To 2 Step -1
Cells(i, 1).EntireRow.Copy
Cells(i, 1).Resize(9).Insert
Cells(i, 1).Offset(1, 11).Resize(9, 244).ClearContents
Next i

Set myR = Range("L2:U2").Resize((myRow - 2) * 10 + 1). _
SpecialCells(xlCellTypeConstants)

For Each myA In myR.Areas
myA.Cells.Copy
Cells(myA.Cells(1, 1).Row, 2).Resize(10).PasteSpecial Transpose:=True
Next myA

Set myR = Range("V2:AE2").Resize((myRow - 2) * 10 + 1). _
SpecialCells(xlCellTypeConstants)

For Each myA In myR.Areas
myA.Cells.Copy
Cells(myA.Cells(1, 1).Row, 4).Resize(10).PasteSpecial Transpose:=True
Next myA

Range("L:AE").EntireColumn.Delete

Range(mySel).Select

With Application
.ScreenUpdating = True
.EnableEvents = True
End With

End Sub




"Martin" wrote in message
...
Hi Bernie,

Really sorry, there is still a misunderstanding.

I am not trying to fit A2:K2 into nine rows. I am copying data from another
worksheet lets say 50 rows of data.

1. Add 9 empty rows after each row of data. After this exercise we should
have 500 rows. 50 with data and another 450 without any data.
2. Now I want to copy the A2:K2 down to A3:K11. (No transposing yet)
3. Then A12:K12 to A13:K21. (No transposing yet)
4. Then A22:K22 to A23:K31 until all the emtpy rows are taken care of (No
transposing yet)
5. Now it's time for transposing. The data in L2:U2 (10 cells) to be copied
into B2:B11 (10 cells) and V2:AE2 into D2:D11.
6. The data in L12:U12 (10 cells) to be copied into B12:B21 (10 cells) and
V12:AE12 into D12:D21
7. The data in L22:U32 (10 cells) to be copied into B22:B31 (10 cells) and
V22:AE22 into D22:D31 until all the 50 sets of have been taken care of.

Hope that is clearer. Thank you for patience.

--
Regards,

Martin


"Bernie Deitrick" wrote:

Martin,

You cannot fit the contents of A2:K2 into nine rows.

A2, B2, C2, D2, E2, F2, G2, H2, I2, J2, K2 is Eleven - NOT nine - values....

?

HTH,
Bernie
MS Excel MVP


"Martin" wrote in message
...
Thanks Bernie,

Please find a better description of what mean below:

I am copying data from one workbook to another. Lets say there could be 50
rows of data starting at row 2. Row 1 represent the
headings. I want the data in L2 to U2 and V2 to AE2 to be displayed in
columns rather than on the row.

I therefore need to add 9 rows for each row of data. The contents of A2 to
K2 need to be copied down to fill the new 9 rows.
The data in L2 to U2 to be transposed into B2:B11 and data in V2 to AE2 to
be transposed D2:D11.

In the next loop I will need add another 9 rows and the contents of A12 to
K12 need to be copied down to fill the new 9 rows.
The data in L12 to U12 to be transposed into B12:B21 and data in V12 to AE12
to be transposed D12:D21.

In the next loop I will need add another 9 rows and the contents of A22 to
K22 need to be copied down to fill the new 9 rows.
The data in L22 to U22 to be transposed into B22:B31 and data in V22 to AE22
to be transposed D22:D31.

Continue this until all the 50 rows are taken care of.
--
Regards,

Martin


"Bernie Deitrick" wrote:

Martin,

1. In a loop I need to insert 9 rows after each row (first row of data is
row 2). The total number of rows transferred vary on a case by case basis.

Which column is this based on?

2. Copy the data in the first row Column A to K into the empty 9 rows in a
loop

A to K is eleven columns, which won't fit into either 9 or 10 rows.... ?
And which column do you want the items tranposed into?

HTH,
Bernie
MS Excel MVP


"Martin" wrote in message
...
Hi everybody,

In a Macro I am transferring data from one worksheet to another. And this is
what I need to do after the data is transferred into the new sheet:

1. In a loop I need to insert 9 rows after each row (first row of data is
row 2). The total number of rows transferred vary on a case by case basis.
2. Copy the data in the first row Column A to K into the empty 9 rows in a
loop
3. The data in the (10) columns L to U need to be transposed in a loop into
column B
4. The data in the (10) columns V to AE need to be transposed in a loop into
column D

Any help much appreciated.

--
Regards,

Martin