Excel 2010 Macro Create Pivot Tables
Pivot tables are the most powerful tools that Excel has to offer. Pivot tables are a fast way to summarize large amounts of data. The name pivot table comes from the ability you have to drag fields in the drop zones and have them recalculate. A basic pivot table delivers a concise summary in seconds. Pivot tables come in many flavors and are the tools of choice for many different uses. Pivot tables can be built to act as the calculation engine to generate reports by store, by style, or to quickly find the top 10 or bottom 5 of anything. It is recommended that you not use VBA to build pivot tables to give to your user. Instead, use pivot tables to extract a summary of data and then take this summary on to other uses. There are many new features offered in Excel 2010. If you use any of these features in VBA, the code will work in Excel 2010 but crash in any legacy versions of Excel.
When adding fields to the Data area of the pivot table, control the many settings available instead of letting Excel’s intellisense decide. Let’s say you are building a report with revenue and you want to sum the revenue. If you do not clearly specify the calculation, Excel will scan through the data in the underlying data. If 100 percent of the revenue columns are numeric, Excel will sum those columns. If one cell is blank or contains text, Excel will decide on that day to count the revenue, and thus, will generate unclear results. Due to this possible variability, never use the DataFields argument in the AddFields method. Instead, change the property of the field to xlDataField. You can then specify the Function to be xlSum. While setting up the data field, you can change several other properties within the same With...End With block. The Position property is helpful when adding multiple fields to the data area. Specify 1 for the first field, 2 for the second field, and so on. By default, Excel will rename a Revenue field to have a odd name like Sum of Revenue. You can use the .Name property to change that heading back to something normal.
To create the pivot table and then copy the results and paste them as values in J12:M13:
Sub CreatePivot()
Dim WSD As Worksheet
Dim PåTCache As PivotCache
Dim PT As PivotTable
Dim PRange As Range
Dim FinalRow As Long
Set WSD = Worksheets(“PivotTable”)
‘ Delete any prior pivot tables
For Each PT In WSD.PivotTables
PT.TableRange2.Clear
Next PT
‘ Define input area and set up a Pivot Cache
FinalRow = WSD.Cells(Application.Rows.Count, 1).End(xlUp).Row
FinalCol = WSD.Cells(1, Application.Columns.Count). _
End(xlToLeft).Column
Set PRange = WSD.Cells(1, 1).Resize(FinalRow, FinalCol)
Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:= _
xlDatabase, SourceData:=PRange.Address)
‘ Create the Pivot Table from the Pivot Cache
Set PT = PTCache.CreatePivotTable(TableDestination:=WSD. _
Cells(2, FinalCol + 2), TableName:=”PivotTable1”)
‘ Turn off updating while building the table
PT.ManualUpdate = True
‘ Set up the row & column fields
PT.AddFields RowFields:=Array(“Region”, “Customer”), _
ColumnFields:=”Product”
‘ Set up the data fields
With PT.PivotFields(“Revenue”)
.Orientation = xlDataField
.Function = xlSum
.Position = 1
.NumberFormat = “#,##0”
.Name = “Revenue “
End With
‘ Calc the pivot table
PT.ManualUpdate = False
PT.ManualUpdate = True
‘Format the pivot table
PT.ShowTableStyleRowStripes = True
PT.TableStyle2 = “PivotStyleMedium10”
With PT
.ColumnGrand = False
.RowGrand = False
.RepeatAllLabels xlRepeatLabels ‘ New in Excel 2010
End With
Set PT = PTCache.CreatePivotTable(TableDestination:=WSD. _
Cells(2, FinalCol + 2), TableName:=”PivotTable1”)
‘ Turn off updating while building the table
PT.ManualUpdate = True
‘ Set up the row fields
PT.AddFields RowFields:=”Product”, ColumnFields:=”Region”
‘ Set up the data fields
With PT.PivotFields(“Revenue”)
.Orientation = xlDataField
.Function = xlSum
.Position = 1
.NumberFormat = “#,##0”
.Name = “Revenue “
End With
With PT
.ColumnGrand = False
.RowGrand = False
.NullString = “0”
End With
‘ Calc the pivot table
PT.ManualUpdate = False
PT.ManualUpdate = True
‘ PT.TableRange2 contains the results. Move these to J12
‘ as just values and not a real pivot table.
PT.TableRange2.Offset(1, 0).Copy
WSD.Cells(5 + PT.TableRange2.Rows.Count, FinalCol + 2). _
PasteSpecial xlPasteValues
‘ At this point, the worksheet looks like Figure 13.10
‘ Stop
‘ Delete the original Pivot Table & the Pivot Cache
PT.TableRange2.Clear
Set PTCache = Nothing
WSD.Activate
Range(“J12”).Select
End Sub
Count Number of Records
The .Function property of the data fields has always been .xlSum. There are a total of 11 functions available: xlSum, xlCount, xlAverage, xlStdDev, xlMin, xlMax, and so on. The only function that works for text fields is Count.
To count the number of records, and thus the number of orders, add a text field to the data area and choose .xlCount as the function.
With PT.PivotFields(“Region”)
.Orientation = xlDataField
.Function = xlCount
.Position = 1
.NumberFormat = “#,##0”
.Name = “# of Orders “
End With
Eliminate Blank Cells
If you have some clients who were new in year 2, their sales will appear blank in year 1. People using Excel 97 or later can replace blank cells with zeros. In the Excel interface, find the setting on the Layout & Format tab of the PivotTable Options dialog box. Select For Empty Cells, Show option and type 0 in the box. The same way to do this in VBA is to set the NullString property for the pivot table to “0”:
PT.NullString = “0”
Using AutoSort to Control the Sort Order
The Excel interface offers an AutoSort option that allows you to show clients in descending order based on revenue. The same way to do this in VBA is to sort the product field by descending revenue using the AutoSort method:
PT.PivotFields(“Customer”).AutoSort Order:=xlDescending, _
Field:=”Revenue “