PivotCode

[mrinal saha]

Hi Folks,

I have a code for pivot where in I am trying to get the values in column but not able to get through.

Actual Pivot Required

<TABLE style="WIDTH: 789pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=1051 border=0><COLGROUP><COL style="WIDTH: 69pt; mso-width-source: userset; mso-width-alt: 3364" width=92><COL style="WIDTH: 87pt; mso-width-source: userset; mso-width-alt: 4242" width=116><COL style="WIDTH: 125pt; mso-width-source: userset; mso-width-alt: 6107" width=167><COL style="WIDTH: 215pt; mso-width-source: userset; mso-width-alt: 10459" width=286><COL style="WIDTH: 52pt; mso-width-source: userset; mso-width-alt: 2523" width=69><COL style="WIDTH: 89pt; mso-width-source: userset; mso-width-alt: 4315" width=118><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3181" width=87><COL style="WIDTH: 87pt; mso-width-source: userset; mso-width-alt: 4242" width=116><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl64 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; WIDTH: 69pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=92 height=17> </TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: #ece9d8; WIDTH: 87pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=116> </TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: #ece9d8; WIDTH: 125pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=167> </TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: #ece9d8; WIDTH: 215pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=286> </TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: #ece9d8; WIDTH: 52pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=69> </TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: #ece9d8; WIDTH: 89pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=118> </TD><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; WIDTH: 65pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=87> Data </TD><TD class=xl68 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: #ece9d8; WIDTH: 87pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=116> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl64 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Book Date</TD><TD class=xl64 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Trans Type</TD><TD class=xl64 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Deal</TD><TD class=xl64 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Member Name</TD><TD class=xl64 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">ICI ID</TD><TD class=xl64 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Plan Name</TD><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"> Sum of Cost </TD><TD class=xl67 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"> Sum of Proceeds </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl69 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17>5/18/2011</TD><TD class=xl64 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Interest Income</TD><TD class=xl64 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">my company</TD><TD class=xl64 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">abc.</TD><TD class=xl64 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>0000</TD><TD class=xl64 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">xxxxx</TD><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"> - </TD><TD class=xl67 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"> 4,366 </TD></TR></TBODY></TABLE>

After writing code this is what I am getting:-

<TABLE style="WIDTH: 882pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=1175 border=0><COLGROUP><COL style="WIDTH: 128pt; mso-width-source: userset; mso-width-alt: 6253" width=171><COL style="WIDTH: 317pt; mso-width-source: userset; mso-width-alt: 15433" width=422><COL style="WIDTH: 66pt; mso-width-source: userset; mso-width-alt: 3218" width=88><COL style="WIDTH: 87pt; mso-width-source: userset; mso-width-alt: 4242" width=116><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 89pt; mso-width-source: userset; mso-width-alt: 4315" width=118><COL style="WIDTH: 81pt; mso-width-source: userset; mso-width-alt: 3949" width=108><COL style="WIDTH: 66pt; mso-width-source: userset; mso-width-alt: 3218" width=88><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; WIDTH: 128pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=171 height=17>Book Date</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; WIDTH: 317pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=422>Trans Type</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; WIDTH: 66pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=88>Deal</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; WIDTH: 87pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=116>Member Name</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64>ICI ID</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; WIDTH: 89pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=118>Plan Name</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; WIDTH: 81pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=108>Data</TD><TD class=xl66 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; WIDTH: 66pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=88>Total</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl67 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17>5/2/2011</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Interest Income</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">My company.</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">abc</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>0000</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">xxxx</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Sum of Cost</TD><TD class=xl66 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>0</TD></TR></TBODY></TABLE>


code piece

Sub cs_EIPA()
Dim ws As Worksheet
Dim pt As PivotTable
Dim prng As Range
Dim lr, lc As Integer
Dim ptcache As PivotCache
Dim pf As PivotField
'******Delete any prior pivot tables
For Each pt In ActiveSheet.PivotTables
pt.TableRange1.Clear
Next pt

'*******Define input area and set up a Pivot Cache

Sheets("TSR- Input").Activate
lr = Sheets("TSR- Input").UsedRange.Rows.Count
lc = Sheets("TSR- Input").UsedRange.Columns.Count

For Each cel In Range("a:a").Cells
If Len(cel) > 0 Then
If Left(cel, 3) = "Lot" Then
rownum = cel.Row
Set prng = ActiveSheet.Cells(rownum, 1).Resize(lr, lc)
Exit For
End If
End If
Next cel
Set ptcache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=prng.Address)
Set pt = ptcache.CreatePivotTable(tabledestination:=Sheets("TSR-Pivot").Range("a2"), tablename:="PivotTable 1")
'***Turn off updating while building the table
'pt.ManualUpdate = True
'*** Set up the row & column fields
pt.AddFields RowFields:=Array("Book Date", "Trans Type", "Deal", "Member Name", "ICI ID", "Plan Name")
'ColumnFields:=Array("Cost", "Proceeds")


' pt.AddDataFields
' pt.PivotFields ("Cost"), xlSum
' pt.AddDataField
' pt.PivotFields ("Proceeds"), xlSum





' '*** Set up the data fields
With pt.PivotFields("Cost")
.Orientation = xlDataField
.Function = xlSum
.Position = 1
End With
With pt.PivotFields("Proceeds")
.Orientation = xlDataField
.Function = xlSum
.Position = 2
End With
end sub

Would be a great help

thankssss
Mrinal

 

[Richard Schollar]

Hi

Try adding:

pt.DataPivotField.Orientation = xlColumnField

at the end