Why can't I get each Data Field in a new Pivot Table to have its own column?

USAMax

Well-known Member
Joined
May 31, 2006
Messages
846
Office Version
  1. 365
Platform
  1. Windows
I am creating a new Pivot Table and everything works except when I add the second and third Data Field. While each Data Field shows up under Values on the Pivot Table, the second and third field overwrites the first field in the fifth column.

How do I get the second Data Field to show-up in the sixth column and the third Data Field to show-up in the seventh column?

Code:
Option Explicit

Sub CreatePivotTable()
    Dim objTable As Object
    Dim objField As Object
    Dim DataSheet As Worksheet

    Set DataSheet = Sheets("Combined Data")
    DataSheet.Select
    DataSheet.Range("A1").Select
    Set objTable = DataSheet.PivotTableWizard

    Set objField = objTable.PivotFields("BU")
    objField.Orientation = xlRowField
    
    Set objField = objTable.PivotFields("Material")
    objField.Orientation = xlRowField
    'objField.Orientation = xlColumnField
    
    Set objField = objTable.PivotFields("Updated Descritpion")
    objField.Orientation = xlRowField
    objField.Caption = "Material Descritpion"
    'objField.Orientation = xlDataField
    
    Set objField = objTable.PivotFields("Tactical Plan in Tracker")
    objField.Orientation = xlPageField
    objField.Caption = "Tactical Plan"
    
    Set objField = objTable.PivotFields("2016 Invoice Quantity")
    objField.Orientation = xlDataField
    objField.Function = xlSum
    objField.NumberFormat = "#,##0"

    Set objField = objTable.PivotFields("2016 VPM")
    objField.Orientation = xlDataField
    objField.Function = xlSum
    objField.NumberFormat = "$ #,##0"

    Set objField = objTable.PivotFields("2016 Revenue")
    objField.Orientation = xlDataField
    objField.Function = xlSum
    objField.NumberFormat = "$ #,##0"

End Sub
 
Last edited:

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Forum statistics

Threads
1,223,903
Messages
6,175,286
Members
452,631
Latest member
a_potato

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top