Modifying excel data through VBA

[chakalido]

Hello!
I have some trouble changing this excel data from format 1 to format 2, I told my boss that I could doet but I found it harder that what I expected. the problem is that I need to rearrange the information, such as the numero, which needs to be put on parallel to the data and not over it and also the dayssince, which tells us the total days since today until the date in the raw. putting all this information in correct order and taking out the totals in a new page is not that much problem but the problem is reading it as the data comes in random length series. Which code tips or formulas should I use and in which order? I am planning to use first a boolean with the function isnumeric to read the blocks and then later i don't know what to use to load it to a matrix (range?) it seems easy to doet with one but guys got 15000 data to manage and the last day I spent 4 hours just arranging it. please I need help otherwise I could get fired
Any kind of help or advice about the order to follow is very welcome. Thank you very much

Data model:

	numero	1235			date
a	0,730229844	0,824992712	0,8910754	0,207179103	24/01/00
b	0,093533973	0,742954165	0,447543154	0,789561771	25/01/00
c	0,445388599	0,261871006	0,848441355	0,16884017	26/01/00
d	0,939590924	0,054993446	0,270982266	0,401500165	27/01/00
e	0,795077783	0,256192901	0,390648391	0,963056254	28/01/00
f	0,431519688	0,564159749	0,034432887	0,333556217	29/01/00
g	0,146387455	0,872931907	0,38941544	0,159426696	30/01/00
h	0,530462297	0,212115688	0,37363141	0,182392945	31/01/00
i	0,51476049	0,548201772	0,976150845	0,098851525	01/02/00
j	0,711398072	0,067160626	0,687721241	0,027383706	02/02/00
k	0,771788243	0,747426884	0,784247298	0,791044794	03/02/00
l	0,658791236	0,019660898	0,314133383	0,145061327	04/02/00
m	0,193252945	0,583310055	0,968759682	0,31653567	05/02/00
n	0,22068699	0,31030548	0,033796761	0,924544293	06/02/00
ñ	0,362384231	0,991565828	0,481601275	0,956265663	07/02/00
o	0,58239276	0,375942528	0,535784077	0,924493567	08/02/00
p	0,236515935	0,557750311	0,023441762	0,910243847	09/02/00
q	0,606452979	0,76436205	0,320071019	0,161598741	10/02/00
r	0,006850638	0,08709637	0,403407667	0,306409466	11/02/00
s	0,2899103	0,315586138	0,980479148	0,490035707	12/02/00
t	0,650660515	0,295493944	0,878186674	0,970512702	13/02/00
u	0,038610083	0,550579862	0,109856038	0,417289966	14/02/00
	-	-		-
	--	total		12345
	numero	1234
v	0,562928397	0,122741418	0,246706341	0,236683886	14/02/00
w	0,010844499	0,814120923	0,168504276	0,128929831	15/02/00
x	0,560163389	0,290640133	0,855471821	0,525374957	16/02/00
y	0,050205549	0,853065673	0,164762477	0,085602949	17/02/00
z	0,316254545	0,034471432	0,957811404	0,599870692	18/02/00

total: 98348934

RESULTS FORMAT

name	value1	value2	value3	value4	date	days since	number
a	0,96744616	0,207810176	0,969817705	0,449287995	24/01/00	100	1235
b	0,61571615	0,152172898	0,607634065	0,846259443	25/01/00	100	1235
c	0,819513864	0,73445847	0,667510376	0,641227101	26/01/00	100	1235
d	0,945269902	0,393498731	0,613122045	0,056427727	27/01/00	100	1235
e	0,179357625	0,960015045	0,205928795	0,247176989	28/01/00	100	1235
f	0,495035684	0,991283832	0,22786266	0,002721088	29/01/00	100	1234
g	0,343283785	0,988925028	0,566433174	0,589712543	30/01/00	100	1234
h	0,815500627	0,639647602	0,867941599	0,263627223	31/01/00	100	1234
i	0,22523379	0,56421092	0,331017485	0,993942348	01/02/00	100	1234
j	0,304904842	0,327341607	0,365227163	0,755969165	02/02/00	100	1234
k	0,097349737	0,491324378	0,550311631	0,528461748	03/02/00	100	1234
l	0,608801789	0,797105591	0,531012006	0,262051007	04/02/00	100	1234
m	0,74772918	0,294153329	0,222966235	0,578013616	05/02/00	100	1234
n	0,394783281	0,670594187	0,97543812	0,134321771	06/02/00	100	1234
ñ	0,498087609	0,029391032	0,222251698	0,068283039	07/02/00	100	1234
o	0,923981589	0,349491141	0,778659265	0,509704437	08/02/00	100	1234
p	0,914654501	0,015896285	0,92254053	0,381342037	09/02/00	100	1234
q	0,234047584	0,343610951	0,785663513	0,866222469	10/02/00	100	1234
r	0,028946792	0,261428832	0,299741859	0,713603334	11/02/00	100	1234
s	0,591556564	0,7393152	0,687215997	0,087306131	12/02/00	100	1234
t	0,598620672	0,666166765	0,875339028	0,861960226	13/02/00	100	1234
u	0,748634727	0,87394075	0,6401052	0,494120693	14/02/00	100	1234

[Jan Karel Pieterse]

This code should do the trick:

Sub ReshapeData()
    Dim oCell As Range
    Dim sNumero As String
    Application.ScreenUpdating = False
    For Each oCell In Intersect(ActiveSheet.UsedRange, Range("A:A"))
        If IsNumeric(oCell.Value) And Not IsEmpty(oCell.Value) Then
            With Worksheets("Sheet2")
                Intersect(oCell.EntireRow, ActiveSheet.UsedRange).Copy
                .Paste .Range("A" & .Rows.Count).End(xlUp).Offset(1)
                .Range("A" & .Rows.Count).End(xlUp).End(xlToRight).Offset(, 1).Value = sNumero
            End With
        ElseIf oCell.Value = "numero" Then
            sNumero = oCell.Offset(, 1).Value
        End If
    Next
    Application.ScreenUpdating = False
    Application.CutCopyMode = False
End Sub