Need code for transposing data

[Krishnakumar]

Hi All,

My orginal data looks like this (SAP generated)

stock.xls
	A	B	C	D	E	F	G	H	I	J
1
2		Plant		1011	abc ltd
3		Material		x1010001
4		Description		item descr1
5
6		Stock on				01.04.2005		2,974		PC
7			Receipts Total				21,250		PC
8			Issues Total				18,693		PC
9		Stock on				05.04.2005		5,531		PC
10
11		Plant		1011	abc ltd
12		Material		x1010002
13		Description		item descr2
14
15		Stock on				01.04.2005		10,085		PC
16			Receipts Total				62,880		PC
17			Issues Total				38,400		PC
18		Stock on				05.04.2005		34,565		PC
19
stock

And I need the result sheet like the following..

stock.xls
	L	M	N	O	P	Q
3	Material	Description	Opng stock	Receipts Total	Issues Total	Clsg Stock
4	x1010001	item descr1	2,974	21250	18693	5,531
5	x1010002	item descr2	10,085	62880	38400	34,565
6
stock

Any help would be appreciated!

 

[Andrew Poulsom]

Try this:

Sub Test()
    Dim Sh As Worksheet
    Dim LastRow As Long
    Dim i As Long
    Dim r As Long
    Set Sh = Worksheets("stock")
    LastRow = Sh.Range("B65536").End(xlUp).Row
    r = 3
    With Sh
        .Cells(r, 12).Value = "Material"
        .Cells(r, 13).Value = "Description"
        .Cells(r, 14).Value = "Opng stock"
        .Cells(r, 15).Value = "Receipts Total"
        .Cells(r, 16).Value = "Issues Total"
        .Cells(r, 17).Value = "Clsg Stock"
    End With
    r = r + 1
    For i = 2 To LastRow Step 9
        With Sh
            .Cells(r, 12).Value = .Cells(i + 1, 4).Value
            .Cells(r, 13).Value = .Cells(i + 2, 4).Value
            .Cells(r, 14).Value = .Cells(i + 4, 8).Value
            .Cells(r, 15).Value = .Cells(i + 5, 7).Value
            .Cells(r, 16).Value = .Cells(i + 6, 7).Value
            .Cells(r, 17).Value = .Cells(i + 7, 8).Value
        End With
        r = r + 1
    Next i
End Sub

 

[Krishnakumar]

Hi Andrew,

Thanks! Exactly what I wanted.