VBA Pivot Table- Datafields Horizontal to Vertical

[teatimecrumpet]

Hey guys,

I've been following a book on how to create a pivot table using VBA.

The code in the book works fine and it's doing what it's supposed to be doing. But i don't understand how to change how the Datafields are displayed.

It's listing the datafields horizontally one on top of the other in One column.

I want to know how to get the datafields to appear in separate columns (in the datafield area)

Here is the book's code:
Sub CreatePivot2()
' Create pivot table at active cell
' Assumes that the source table is in sheet called Source
ActiveSheet.PivotTableWizard _
SourceType:=xlDatabase, _
SourceData:="'Source'!R1C1:R145C7", _
TableName:="Sales&Trans2"
ActiveSheet.PivotTables("Sales&Trans2").AddFields _
RowFields:=Array("Store City", "Store Type"), _
ColumnFields:="Period", _
PageFields:="Year"
With ActiveSheet.PivotTables("Sales&Trans2")
With .PivotFields("Transactions")
.Orientation = xlDataField
.Position = 1
End With
With .PivotFields("Sales")
.Orientation = xlDataField
.Position = 2
End With
End With
End Sub

And here is what I'd like the Pivot table to look like instead:

http://www.mrexcel.com/articles/fill-pivot-blank-cells.php


Thanks,
Mike

 

[teatimecrumpet]

Sorry, ignore that link I'll just post this instead

What I get:

<table x:str="" style="border-collapse: collapse; width: 298pt;" border="0" cellpadding="0" cellspacing="0" width="398"><col style="width: 80pt;" width="107"> <col style="width: 48pt;" width="64"> <col style="width: 122pt;" width="163"> <col style="width: 48pt;" width="64"> <tbody><tr style="height: 12.75pt;" height="17"> <td id="_x0000_s4097" x:autofilter="all" x:autofilterrange="$A$1:$C$1" style="height: 12.75pt; width: 80pt;" height="17" width="107"> <table x:str="" style="border-collapse: collapse; width: 541pt;" border="0" cellpadding="0" cellspacing="0" width="719"><col style="width: 149pt;" width="198"> <col style="width: 71pt;" width="94"> <col style="width: 124pt;" width="165"> <col style="width: 93pt;" width="124"> <col style="width: 104pt;" width="138"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt; width: 149pt;" id="_x0000_s4099" height="17" width="198">Vendor</td> <td class="xl65" style="border-left: medium none; width: 71pt;" u1:autofilter="all" width="94">Region</td> <td class="xl65" style="border-left: medium none; width: 124pt;" u1:autofilter="all" width="165">Data</td> <td class="xl65" style="border-left: medium none; width: 93pt;" id="_x0000_s4099" width="124">Total</td> <td class="xl66" style="border-left: medium none; width: 104pt;" width="138">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="border-top: medium none; height: 12.75pt; width: 149pt;" height="17" width="198">mike</td> <td class="xl65" style="border-top: medium none; border-left: medium none; width: 71pt;" width="94">North</td> <td class="xl65" style="border-top: medium none; border-left: medium none; width: 124pt;" width="165">Sum of Apples</td> <td class="xl67" style="border-top: medium none; border-left: medium none; width: 93pt;" x:num="" u1:num="" width="124">1</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="border-top: medium none; height: 12.75pt; width: 149pt;" height="17" width="198">
</td> <td class="xl65" style="border-top: medium none; border-left: medium none; width: 71pt;" width="94">
</td> <td class="xl65" style="border-top: medium none; border-left: medium none; width: 124pt;" width="165">Sum of Oranges</td> <td class="xl67" style="border-top: medium none; border-left: medium none; width: 93pt;" x:num="" u1:num="" width="124">11</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="border-top: medium none; height: 12.75pt; width: 149pt;" height="17" width="198">
</td> <td class="xl65" style="border-top: medium none; border-left: medium none; width: 71pt;" width="94">
</td> <td class="xl65" style="border-top: medium none; border-left: medium none; width: 124pt;" width="165">Sum of Bananas</td> <td class="xl67" style="border-top: medium none; border-left: medium none; width: 93pt;" x:num="" u1:num="" width="124">10</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="border-top: medium none; height: 12.75pt; width: 149pt;" height="17" width="198">
</td> <td class="xl65" style="border-top: medium none; border-left: medium none; width: 71pt;" width="94">West</td> <td class="xl65" style="border-top: medium none; border-left: medium none; width: 124pt;" width="165">Sum of Apples</td> <td class="xl67" style="border-top: medium none; border-left: medium none; width: 93pt;" x:num="" u1:num="" width="124">2</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="border-top: medium none; height: 12.75pt; width: 149pt;" height="17" width="198">
</td> <td class="xl65" style="border-top: medium none; border-left: medium none; width: 71pt;" width="94">
</td> <td class="xl65" style="border-top: medium none; border-left: medium none; width: 124pt;" width="165">Sum of Oranges</td> <td class="xl67" style="border-top: medium none; border-left: medium none; width: 93pt;" x:num="" u1:num="" width="124">5</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="border-top: medium none; height: 12.75pt; width: 149pt;" height="17" width="198">
</td> <td class="xl65" style="border-top: medium none; border-left: medium none; width: 71pt;" width="94">
</td> <td class="xl65" style="border-top: medium none; border-left: medium none; width: 124pt;" width="165">Sum of Bananas</td> <td class="xl67" style="border-top: medium none; border-left: medium none; width: 93pt;" x:num="" u1:num="-39955.839999999997" width="124">6</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="border-top: medium none; height: 12.75pt; width: 149pt;" height="17" width="198">
</td> <td class="xl65" style="border-top: medium none; border-left: medium none; width: 71pt;" width="94">South</td> <td class="xl65" style="border-top: medium none; border-left: medium none; width: 124pt;" width="165">Sum of Apples</td> <td class="xl67" style="border-top: medium none; border-left: medium none; width: 93pt;" x:num="" u1:num="" width="124">7</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="border-top: medium none; height: 12.75pt; width: 149pt;" height="17" width="198">
</td> <td class="xl65" style="border-top: medium none; border-left: medium none; width: 71pt;" width="94">
</td> <td class="xl65" style="border-top: medium none; border-left: medium none; width: 124pt;" width="165">Sum of Oranges</td> <td class="xl67" style="border-top: medium none; border-left: medium none; width: 93pt;" x:num="" u1:num="1057309.11" width="124">8</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="border-top: medium none; height: 12.75pt; width: 149pt;" height="17" width="198">
</td> <td class="xl65" style="border-top: medium none; border-left: medium none; width: 71pt;" width="94">
</td> <td class="xl65" style="border-top: medium none; border-left: medium none; width: 124pt;" width="165">Sum of Bananas</td> <td class="xl67" style="border-top: medium none; border-left: medium none; width: 93pt;" x:num="" u1:num="" width="124">9</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="border-top: medium none; height: 12.75pt; width: 149pt;" height="17" width="198">
</td> <td class="xl65" style="border-top: medium none; border-left: medium none; width: 71pt;" width="94">East</td> <td class="xl65" style="border-top: medium none; border-left: medium none; width: 124pt;" width="165">Sum of Apples</td> <td class="xl67" style="border-top: medium none; border-left: medium none; width: 93pt;" x:num="" u1:num="304283.09" width="124">12</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="border-top: medium none; height: 12.75pt; width: 149pt;" height="17" width="198">
</td> <td class="xl65" style="border-top: medium none; border-left: medium none; width: 71pt;" width="94">
</td> <td class="xl65" style="border-top: medium none; border-left: medium none; width: 124pt;" width="165">Sum of Oranges</td> <td class="xl67" style="border-top: medium none; border-left: medium none; width: 93pt;" x:num="" u1:num="-458241.52" width="124">13</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="border-top: medium none; height: 12.75pt; width: 149pt;" height="17" width="198">
</td> <td class="xl65" style="border-top: medium none; border-left: medium none; width: 71pt;" width="94">
</td> <td class="xl65" style="border-top: medium none; border-left: medium none; width: 124pt;" width="165">Sum of Bananas</td> <td class="xl67" style="border-top: medium none; border-left: medium none; width: 93pt;" x:num="" u1:num="3329265.09" width="124">14</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="border-top: medium none; height: 12.75pt; width: 149pt;" height="17" width="198">
</td> <td class="xl65" style="border-top: medium none; border-left: medium none; width: 71pt;" width="94">
</td> <td class="xl65" style="border-top: medium none; border-left: medium none; width: 124pt;" width="165">
</td> <td class="xl65" style="border-top: medium none; border-left: medium none; width: 93pt;" width="124">
</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td colspan="2" class="xl65" style="height: 12.75pt; width: 220pt;" height="17" width="292">Mike Sum of Apples</td> <td class="xl65" style="border-top: medium none; border-left: medium none; width: 124pt;" width="165">
</td> <td class="xl65" style="border-top: medium none; border-left: medium none; width: 93pt;" x:num="" align="right" width="124">22</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td colspan="2" class="xl65" style="height: 12.75pt; width: 220pt;" height="17" width="292">Mike Sum of oranges</td> <td class="xl65" style="border-top: medium none; border-left: medium none; width: 124pt;" width="165">
</td> <td class="xl65" style="border-top: medium none; border-left: medium none; width: 93pt;" x:num="" align="right" width="124">37</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td colspan="2" class="xl65" style="height: 12.75pt; width: 220pt;" height="17" width="292">Mike Sum of bananas</td> <td class="xl65" style="border-top: medium none; border-left: medium none; width: 124pt;" width="165">
</td> <td class="xl65" style="border-top: medium none; border-left: medium none; width: 93pt;" x:num="" align="right" width="124">39</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">
</td> </tr> </tbody></table></td> <td id="_x0000_s4098" x:autofilter="all" style="width: 48pt;" width="64">
</td> <td id="_x0000_s4099" x:autofilter="all" style="width: 122pt;" width="163">
</td> <td style="width: 48pt;" width="64">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td>
</td> <td>
</td> <td x:num="" align="right">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">



</td> <td>
</td> <td>
</td> <td x:num="" align="right">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">What I'm trying to get:
<table x:str="" style="border-collapse: collapse; width: 541pt;" border="0" cellpadding="0" cellspacing="0" width="719"> <col style="width: 149pt;" width="198"> <col style="width: 71pt;" width="94"> <col style="width: 124pt;" width="165"> <col style="width: 93pt;" width="124"> <col style="width: 104pt;" width="138"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt; width: 149pt;" u1:num="3329265.09" height="17" width="198">
</td> <td class="xl65" style="border-left: medium none; width: 71pt;" width="94">
</td> <td class="xl65" style="border-left: medium none; width: 124pt;" u1:str="Data" width="165"> Data </td> <td class="xl65" style="border-left: medium none; width: 93pt;" width="124">
</td> <td class="xl65" style="border-left: medium none; width: 104pt;" width="138">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="border-top: medium none; height: 12.75pt; width: 149pt;" height="17" width="198">Vendor</td> <td class="xl65" style="border-top: medium none; border-left: medium none; width: 71pt;" width="94">Region</td> <td class="xl65" style="border-top: medium none; border-left: medium none; width: 124pt;" u1:str="Sum of Apples" width="165"> Sum of Apples </td> <td class="xl65" style="border-top: medium none; border-left: medium none; width: 93pt;" u1:str="Sum of Oranges" width="124"> Sum of Oranges </td> <td class="xl65" style="border-top: medium none; border-left: medium none; width: 104pt;" u1:str="Sum of Bananas" width="138"> Sum of Bananas </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="border-top: medium none; height: 12.75pt; width: 149pt;" height="17" width="198">Mike</td> <td class="xl65" style="border-top: medium none; border-left: medium none; width: 71pt;" u1:str="North " width="94">North </td> <td class="xl65" style="border-top: medium none; border-left: medium none; width: 124pt;" x:num="" align="right" width="165">1</td> <td class="xl65" style="border-top: medium none; border-left: medium none; width: 93pt;" x:num="" align="right" width="124">11</td> <td class="xl65" style="border-top: medium none; border-left: medium none; width: 104pt;" x:num="" align="right" width="138">10</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="border-top: medium none; height: 12.75pt; width: 149pt;" height="17" width="198">
</td> <td class="xl65" style="border-top: medium none; border-left: medium none; width: 71pt;" width="94">West</td> <td class="xl65" style="border-top: medium none; border-left: medium none; width: 124pt;" x:num="" align="right" width="165">2</td> <td class="xl65" style="border-top: medium none; border-left: medium none; width: 93pt;" x:num="" align="right" width="124">5</td> <td class="xl65" style="border-top: medium none; border-left: medium none; width: 104pt;" x:num="" align="right" width="138">6</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="border-top: medium none; height: 12.75pt; width: 149pt;" height="17" width="198">
</td> <td class="xl65" style="border-top: medium none; border-left: medium none; width: 71pt;" width="94">South</td> <td class="xl65" style="border-top: medium none; border-left: medium none; width: 124pt;" x:num="" align="right" width="165">7</td> <td class="xl65" style="border-top: medium none; border-left: medium none; width: 93pt;" x:num="" align="right" width="124">8</td> <td class="xl65" style="border-top: medium none; border-left: medium none; width: 104pt;" x:num="" align="right" width="138">9</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="border-top: medium none; height: 12.75pt; width: 149pt;" height="17" width="198">
</td> <td class="xl65" style="border-top: medium none; border-left: medium none; width: 71pt;" width="94">East</td> <td class="xl65" style="border-top: medium none; border-left: medium none; width: 124pt;" x:num="" align="right" width="165">12</td> <td class="xl65" style="border-top: medium none; border-left: medium none; width: 93pt;" x:num="" align="right" width="124">13</td> <td class="xl65" style="border-top: medium none; border-left: medium none; width: 104pt;" x:num="" align="right" width="138">14</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl66" style="border-top: medium none; height: 12.75pt;" height="17">Grand Total</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl65" style="border-top: medium none; border-left: medium none; width: 124pt;" x:num="" align="right" width="165">22</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" x:num="" x:fmla="=SUM(D3:D6)" align="right">37</td> <td class="xl65" style="border-top: medium none; border-left: medium none; width: 104pt;" x:num="" align="right" width="138">39</td> </tr> </tbody> </table> </td> <td>
</td> <td>
</td> <td x:num="" align="right">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td>
</td> <td>
</td> <td x:num="" align="right">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td>
</td> <td>
</td> <td x:num="-39955.839999999997" align="right">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td>
</td> <td>
</td> <td x:num="" align="right">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td>
</td> <td>
</td> <td x:num="1057309.11" align="right">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td>
</td> <td>
</td> <td x:num="" align="right">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td>
</td> <td>
</td> <td x:num="304283.09" align="right">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td>
</td> <td>
</td> <td x:num="-458241.52" align="right">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td>
</td> <td>
</td> <td x:num="3329265.09" align="right">
</td> </tr> </tbody></table>

 

[teatimecrumpet]

Just used the macro-recorder the code looks like this:



With ActiveSheet.PivotTables("PivotTable1").DataPivotField
.Orientation = xlColumnField
.Position = 1
End With

Hope this helps someone.