Pivot Table with multiple columns

ExcelChick · Nov 18th 2013

Hello all,

I am trying to create a pivot table that has "Metric" in the Rows and the sum of Q1-Q4 FY13 & 14 into the columns. All of the numerical values sit under the headers Q1 etc. So the data looks like this:

[TABLE="width: 652"]

[tr]

[td]

Metric

[/td]

[td]

Q1 FY13

[/td]

[td]

Q2 FY13

[/td]

[td]

Q3 FY13

[/td]

[td]

Q4 FY13

[/td]

[td]

Q1 FY14

[/td]

[td]

Q2 FY14

[/td]

[td]

Q3 FY14

[/td]

[td]

Q4 FY14

[/td]

[/tr]

[tr]

[td]

Unloaded Chg Payroll

[/td]

[TD="align: right"]101.819[/TD]
[TD="align: right"]95.60698[/TD]
[TD="align: right"]113.3032[/TD]
[TD="align: right"]157.2209[/TD]
[TD="align: right"]231.1385[/TD]
[TD="align: right"]401.4415[/TD]
[TD="align: right"]525.2051[/TD]
[TD="align: right"]473.9572[/TD]

[/tr]

[tr]

[td]

Unloaded Chg Payroll

[/td]

[TD="align: right"]210.7658[/TD]
[TD="align: right"]56.87322[/TD]
[TD="align: right"]55.68585[/TD]
[TD="align: right"]15.40472[/TD]
[TD="align: right"]24.31954[/TD]
[TD="align: right"]39.38726[/TD]
[TD="align: right"]53.65847[/TD]
[TD="align: right"]48.42264[/TD]

[/tr]

[tr]

[td]

Unloaded Chg Payroll

[/td]

[TD="align: right"]-1.73045[/TD]
[TD="align: right"]65.64151[/TD]
[TD="align: right"]135.621[/TD]
[TD="align: right"]59.11858[/TD]
[TD="align: right"]93.33092[/TD]
[TD="align: right"]151.1562[/TD]
[TD="align: right"]205.9247[/TD]
[TD="align: right"]185.8312[/TD]

[/tr]

[tr]

[td]

Unloaded Chg Payroll

[/td]

[TD="align: right"]93.13438[/TD]
[TD="align: right"]15.52834[/TD]
[TD="align: right"]9.75809[/TD]
[TD="align: right"]53.07859[/TD]
[TD="align: right"]83.79554[/TD]
[TD="align: right"]135.713[/TD]
[TD="align: right"]184.8859[/TD]
[TD="align: right"]166.8453[/TD]

[/tr]

[/TABLE]

This is what I have - but it's not working

Code

Sub Macro1()
'
' Macro1 Macro
Dim objTable As PivotTable
Dim objField As PivotField




'




    ActiveWorkbook.Sheets("Sup_Data(2)").Select
    Range("A1").Select
    Set objTable = Sheets("Sup_Data(2)").PivotTableWizard
    
    ActiveWindow.DisplayGridlines = False
    
    Set objField = objTable.PivotFields("Metric")
    objField.Orientation = xlRowField
    
    
 With ActiveSheet.PivotTables("PivotTable1").AddDataField.ActiveSheet _
.PivotTables("PivotTable1").PivotFields ("Q1 FY13"), "Q2 FY13", xlSum
.PivotTables("PivotTable1").PivotFields ("Q2 FY13"), "Q2 FY13", xlSum
 .PivotTables("PivotTable1").PivotFields ("Q3 FY13"), "Q2 FY13", xlSum
.PivotTables("PivotTable1").PivotFields ("Q4 FY13"), "Q2 FY13", xlSum
.PivotTables("PivotTable1").PivotFields ("Q1 FY14"), "Q2 FY14", xlSum
.PivotTables("PivotTable1").PivotFields ("Q2 FY14"), "Q2 FY14", xlSum
.PivotTables("PivotTable1").PivotFields ("Q3 FY14"), "Q2 FY14", xlSum
.PivotTables("PivotTable1").PivotFields ("Q4 FY14"), "Q2 FY14", xlSum
 End With
End Sub

Display More

I would also like to format all the fields .NumberFormat = " #,##0" at the same time.
And rename the sheet "Supervised Pivot"

Can anyone assist?

Thanks,

ExcelChick · Nov 19th 2013

Re: Pivot Table with multiple columns

This is what I came up with after looking around the internet - I don't know how to make the pivot fields an array - but this is getting me what I need. Maybe this will help others!

Code

'PIVOT TABLE
Dim objTable As PivotTable
Dim objField As PivotField












    ActiveWorkbook.Sheets("Sup_Data(2)").Select
    Range("A1").Select
    Set objTable = Sheets("Sup_Data(2)").PivotTableWizard




    ActiveWindow.DisplayGridlines = False
    ActiveSheet.Name = "Supervised Pivot"
'
    Set objField = objTable.pivotfields("Metric")
    objField.Orientation = xlRowField








  
    




    Set objField = objTable.pivotfields("Q1 FY13")
    objField.Orientation = xlDataField
    objField.Function = xlSum
     objField.NumberFormat = "#,##0_);[Red](#,##0)"
     objField.Caption = "Q1_FY13"
 
'
     Set objField = objTable.pivotfields("Q2 FY13")
    objField.Orientation = xlDataField
    objField.Function = xlSum
     objField.NumberFormat = "#,##0_);[Red](#,##0)"
     objField.Caption = "Q2_FY13"




     Set objField = objTable.pivotfields("Q3 FY13")
    objField.Orientation = xlDataField
    objField.Function = xlSum
    objField.NumberFormat = "#,##0_);[Red](#,##0)"
    objField.Caption = "Q3_FY13"




     Set objField = objTable.pivotfields("Q4 FY13")
    objField.Orientation = xlDataField
    objField.Function = xlSum
    objField.NumberFormat = "#,##0_);[Red](#,##0)"
    objField.Caption = "Q4_FY13"




     Set objField = objTable.pivotfields("Q1 FY14")
    objField.Orientation = xlDataField
    objField.Function = xlSum
    objField.NumberFormat = "#,##0_);[Red](#,##0)"
    objField.Caption = "Q1_FY14"




    Set objField = objTable.pivotfields("Q2 FY14")
    objField.Orientation = xlDataField
    objField.Function = xlSum
    objField.NumberFormat = "#,##0_);[Red](#,##0)"
    objField.Caption = "Q2_FY14"
 




    Set objField = objTable.pivotfields("Q3 FY14")
    objField.Orientation = xlDataField
    objField.Function = xlSum
    objField.NumberFormat = "#,##0_);[Red](#,##0)"
    objField.Caption = "Q3_FY14"




    Set objField = objTable.pivotfields("Q4 FY14")
    objField.Orientation = xlDataField
    objField.Function = xlSum
    objField.NumberFormat = "#,##0_);[Red](#,##0)"
    objField.Caption = "Q4_FY14"




'Moves Values to Columns in pivot
 Set objField = objTable.pivotfields("Data")
objField.Orientation = xlColumnField

Display More

Herbds7 · Nov 19th 2013

Re: Pivot Table with multiple columns

Excel 2010 with free PowerPivot Add-In
Using DAX, not VBA macros.
With Fiscal Year/Month/Quarters.
May it serve the community.
http://www.mediafire.com/downl…2pjd9bji38l/11_18_13.xlsx

Pivot Table with multiple columns

Participate now!

Share