Changing data orientation from one sheet to another

[YMTEO]

Hi,

I have a report submitted which looks like a pivot table format and would
like to change it to another orientation in another worksheet.
Is there a faster way to do that?

Original format:-
Customer Country1 Name Data Aug-07 Sep-07 Oct-07
Cust A Malaysia Apple Sales 7,253 2,115
Cust A Malaysia Apple Volume 480 140
Cust A Malaysia Apple SASP 15.110 15.110
Cust A Malaysia Orange Sales 3,050 10,750 11,150
Cust A Malaysia Orange Volume 15,250 53,750 55,750
Cust A Malaysia Orange SASP 0.200 0.200 0.200
Cust B US Pear Sales 2,369
Cust B US Pear Volume 360
Cust B US Pear SASP 6.580
Cust B US Orange Sales 6,075 9,113 13,669
Cust B US Orange Volume 900 1,350 2,025
Cust B US Orange SASP 6.750 6.750 6.750
Cust C China Pear Sales 570 570 760
Cust C China Pear Volume 30 30 35
Cust C China Pear SASP 19.000 19.000 21.714
Cust D US Cherry Sales 5,832 4,860
Cust D US Cherry Volume 129,600 108,000
Cust D US Cherry SASP 0.045 0.045
Cust D US Orange Sales 720 24,000 18,000
Cust D US Orange Volume 4 120 90
Cust D US Orange SASP 200.000 200.000 200.000


change it to another format in another worksheet:-

Customer Country1 Name Data Sales Volume SASP
Cust A Malaysia Apple Aug-07 7252.8 480 15.11
Cust A Malaysia Apple Oct-07 2115.4 140 15.11
Cust A Malaysia Orange Aug-07 3,050 15,250 0.200
Cust A Malaysia Orange Sep-07 10,750 53,750 0.200
Cust A Malaysia Orange Oct-07 11,150 55,750 0.200
etc
etc

It would even be better if the 2nd worksheet link to the first data source.

 

[Don Guillett]

Instead of that why not just employ adding/hiding/moving columns back and
forth. A macro can do it for you. Just record it and assign to a shape or
button from the forms toolbar.

 

[YMTEO]

Hi Don,

Could you guide me how to do that?
How to assign the macro? I am not good at that.
There are lots of rows and would it tak up lots of time in doing it?


Thanks
Yimin

 

[Don Guillett]

If desired, send your workbook to my address below along with snippets of
these messages along with a clear explanation of what you want and
before/after examples. Then, I can take a look.

 

[Don Guillett]

maybe this executed from the source sheet
Sub leusht2()
With Sheets("destinationsheetnamehere")
dlr = .Cells(Rows.Count, 1).End(xlUp).Row
.Range("a2:g" & dlr).ClearContents
For i = 2 To cells(rows.count,1).end(xlup).row Step 3
Set mc = Cells(i, "d")
Set dmc = .Cells(i, "a")
If mc.Offset(, 1) <> "" Then
dmc.Offset(, 0) = mc.Offset(, -3)
dmc.Offset(, 1) = mc.Offset(1, -2)
dmc.Offset(, 2) = mc.Offset(2, -1)
dmc.Offset(, 3) = Range("e1")
dmc.Offset(, 4) = mc.Offset(, 1)
dmc.Offset(, 5) = mc.Offset(1, 1)
dmc.Offset(, 6) = mc.Offset(2, 1)
End If
If mc.Offset(, 2) <> "" Then
dmc.Offset(1, 0) = mc.Offset(, -3)
dmc.Offset(1, 1) = mc.Offset(1, -2)
dmc.Offset(1, 2) = mc.Offset(2, -1)
dmc.Offset(1, 3) = Range("f1")
dmc.Offset(1, 4) = mc.Offset(, 2)
dmc.Offset(1, 5) = mc.Offset(1, 2)
dmc.Offset(1, 6) = mc.Offset(2, 2)
End If
If mc.Offset(, 3) <> "" Then
dmc.Offset(2, 0) = mc.Offset(, -3)
dmc.Offset(2, 1) = mc.Offset(1, -2)
dmc.Offset(2, 2) = mc.Offset(2, -1)
dmc.Offset(2, 3) = Range("g1")
dmc.Offset(2, 4) = mc.Offset(, 3)
dmc.Offset(2, 5) = mc.Offset(1, 3)
dmc.Offset(3, 6) = mc.Offset(2, 3)
End If
Next i
.Columns(1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End With
End Sub

 

[YMTEO]

Hi,

Thanks.
It is working fine.
It is so much easiler now.

THANKS A LOT......