Copy dynamic Range and pasteSpecial(Transpose)

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

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

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

By: odezi

Leave a Reply

Your email address will not be published. Required fields are marked *