Copy Transpose with Macro

[primala]

Hi Guys,

I have data of material installed on each (oil) Well, the number of well is one at the minimum, and up to 60 wells.
Here's the detail :

Column A : sequence number
Column B : item number of material
Column C : description of material
Column D : unit of material
Column E : quantity of material installed of Well # 1
Column F : quantity of material installed of Well # 2
Cell value in E3 (header) : Well # 1
Cell value in F3 (header) : Well # 2


What I want to do :

If I have only 2 wells of material installed (E4 & F4)
Then I should get :
- copy of the first row of material installed (starting from row # 4), from column A to D
into the last row of data, and down 1 row
- copy of the header (Well # 1) in column E
- copy the quantity of Well # 1 (from E4) into column F

- copy of the first row of material installed (starting from row # 4), from column A to D
into the last row of data, and down 1 row
- copy of the header (Well # 2) in column E
- copy the quantity of Well # 2 (from F4) into column F

For more details please find the sample data :

You may found the link below :

Matl Installed - Excel - Download - 4shared
Screen Shot - Download - 4shared


I really hope anyone of you could help me out.
Thanks in advance

 

[primala]

It was copied to Sheets 2, but all of the data were duplicated. When the row number = 40 and the number of well = 7, the code gave us 560 records, not 7 times 40 = 280 records.

 

[patel45]

attach the new file

 

[primala]

Hi patel45,

Thanks so much for your big effort and kind support, finally I choose to use your magic code, and made a modification to identify the number of wells using WorksheetFunction(CountA(Range("E3:BL3")) for nw --> 60 columns at maximum
God bless you, thank you.