Copying complex data format

[Durga]

Hi,

I have a set of data in the following format which I want to have in the simplified format as mentioned below on Sheet2.

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	Sheet1
2	ONMOB123	0	0	0	0
3		0	904030	79066057	0
4
5	DASONMOB	0	0	0	1000
6		39142	0	0	0
7
8	OLDMCA	0	0	0	0
9		0	0	0	0
10
11	Sheet2
12
13	ONMOB123	0	0	0	0	0	904030	79066057	0
14	DASONMOB	0	0	0	1000	39142	0	0	0
15	OLDMCA	0	0	0	0	0	0	0	0
16
Sheet1

Since the data volume is quite huge, can it possible to write a macro for this.
please help.

Thanks in Advance.

 

[Peter_SSs]

Durga

See if this is what you want.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Rearrange()
    <SPAN style="color:#00007F">Dim</SPAN> ws1 <SPAN style="color:#00007F">As</SPAN> Worksheet
    <SPAN style="color:#00007F">Dim</SPAN> ws2 <SPAN style="color:#00007F">As</SPAN> Worksheet
    <SPAN style="color:#00007F">Dim</SPAN> lr <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> nr <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
    
    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN>
    Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN>
    <SPAN style="color:#00007F">Set</SPAN> ws1 = Sheets("Sheet1")
    <SPAN style="color:#00007F">Set</SPAN> ws2 = Sheets("Sheet2")
    lr = ws1.Range("A" & Rows.Count).End(xlUp).Row
    nr = 1
    <SPAN style="color:#00007F">For</SPAN> r = 1 <SPAN style="color:#00007F">To</SPAN> lr
        <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> (IsEmpty(ws1.Cells(r, 1))) <SPAN style="color:#00007F">Then</SPAN>
            <SPAN style="color:#00007F">With</SPAN> ws1.Cells(r, 1)
                .Resize(1, 5).Copy _
                    Destination:=ws2.Cells(nr, 1)
                .Offset(1, 1).Resize(1, 4).Copy _
                    Destination:=ws2.Cells(nr, 6)
                nr = nr + 1
            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    <SPAN style="color:#00007F">Next</SPAN> r
    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN>
    Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[Durga]

Hi,


Thanks a TON Peter

This is what I was lookin' For.

Regards,