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