Copy dynamic Range and pasteSpecial(Transpose)

[odezi455]

Hi all, I need help in structuring a lot of data file and I was wondering If anyone could help me.
what I want is to search for cell that starts with "CP" in range (A:A) then copy the next set of row untill the next "CP" and paste(transpose).
This is how the data is at the moment:

CP,00001
Date,15/06/2014
1
2
6
9
85
24
32
1485
25
1
101
5
87
9
6
3
7
1
2
CP,00502
Date,14/06/2014
5
69
35
78.6
36.9
85.8
8569.5
78.9
8
874
2
2
8
2
5
2
5
2
CP,04003
Date,19/06/2014
8
7
8
9
52
12
14
47
25
69
10
10
CP,90004
Date,16/06/2014
36
7.3
3.6
36
58
25
21
CP,00035
Date,1/06/2014
78.6
36.9
85.8
8569.5
78.9
8
874
2
2
1
2
6
9
CP,99006
Date,12/06/2014
1
2
5
3
8
87
96
66
55

<tbody>
</tbody>

and I want it to be in this form

CP,00001	Date,15/06/2014	1	2	6	9	85	24	32	1485	25	1	101	5	87	9	6	3	7	1
CP,00502	Date,14/06/2014	5	69	35	78.6	36.9	85.8	8570	78.9	8	874	2	2	8	2	5	2	5	2
CP,04003	Date,19/06/2014	8	7	8	9	52	12	14	47	25	69	10	10
CP,90004	Date,16/06/2014	36	7.3	3.6	36	58	25	21
CP,00035	Date,1/06/2014	78.6	36.9	85.8	8570	78.9	8	874	2	2	1	2	6	9
CP,99006	Date,12/06/2014	1	2	5	3	8	87	96	66	55	87

<tbody>
</tbody>

I will be very grateful if some one could help me with the VBA code for this.
Thanks in advance

 

[Oeldere]

why do you need the values in a row?

If you want to analyze the data, (e.g. with an pivot table) a verticaly format is much better.

 

[odezi455]

why do you need the values in a row?

If you want to analyze the data, (e.g. with an pivot table) a verticaly format is much better.

Hi,
I have several hundreds of files in this format and I want to organise it into a database... in row format.
Thanks