Microsoft Office Tutorials and References
In Depth Information
Creating a More Complex Pivot Table
Another option is to insert a new column in the table and create a formula to calculate
the difference between the budget and actual amounts. If the data is from an external
source (rather than in a worksheet), that option may not be possible.
The code that created the pivot table
Here’s the VBA code that created the pivot table:
Sub CreatePivotTable()
Dim PTcache As PivotCache
Dim PT As PivotTable
Application.ScreenUpdating = False
‘ Delete PivotSheet if it exists
On Error Resume Next
Application.DisplayAlerts = False
Sheets(“PivotSheet”).Delete
On Error GoTo 0
‘ Create a Pivot Cache
Set PTcache = ActiveWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:=Range(“A1”).CurrentRegion.Address)
‘ Add new worksheet
Worksheets.Add
ActiveSheet.Name = “PivotSheet”
ActiveWindow.DisplayGridlines = False
‘ Create the Pivot Table from the Cache
Set PT = ActiveSheet.PivotTables.Add( _
PivotCache:=PTcache, _
TableDestination:=Range(“A1”), _
TableName:=”BudgetPivot”)
With PT
‘ Add fields
.PivotFields(“Category”).Orientation = xlPageField
.PivotFields(“Division”).Orientation = xlPageField
.PivotFields(“Department”).Orientation = xlRowField
.PivotFields(“Month”).Orientation = xlColumnField
.PivotFields(“Budget”).Orientation = xlDataField
.PivotFields(“Actual”).Orientation = xlDataField
.DataPivotField.Orientation = xlRowField
‘ Add a calculated field to compute variance
.CalculatedFields.Add “Variance”, “=Budget-Actual”
.PivotFields(“Variance”).Orientation = xlDataField
‘ Specify a number format
 
Search JabSto ::




Custom Search