Creating Multiple Pivot Tables

The final example creates a series of pivot tables that summarize data collected in a customer survey. That data is stored in a worksheet database (see Figure 17-6) and consists of 150 rows. Each row contains the respondent's sex plus a numerical rating using a 1 “5 scale for each of the 14 survey items.

Figure 17-6: Creating a series of pivot tables will summarize this survey data.

CD-ROM  

This workbook, named  survey data pivot tables.xlsm , is available on the companion CD-ROM.

Figure 17-7 shows a few of the 28 pivot tables produced by the macro. Each survey item is summarized in two pivot tables (one showing percentages, and one showing the actual frequencies).

Figure 17-7: Six of the 28 pivot tables created by a VBA procedure.

The VBA code that created the pivot tables follows :

 Sub MakePivotTables() '   This procedure creates 28 pivot tables     Dim PTCache As PivotCache     Dim PT As PivotTable     Dim SummarySheet As Worksheet     Dim ItemName As String     Dim Row As Long, Col As Long, i As Long     Application.ScreenUpdating = False '   Delete Summary sheet if it exists     On Error Resume Next     Application.DisplayAlerts = False     Sheets("Summary").Delete     On Error GoTo 0 '   Add Summary sheet     Set SummarySheet = Worksheets.Add     ActiveSheet.Name = "Summary" '   Create Pivot Cache     Set PTCache = ActiveWorkbook.PivotCaches.Create( _       SourceType:=xlDatabase, _       SourceData:=Sheets("SurveyData").Range("A1"). _         CurrentRegion)     Row = 1     For i = 1 To 14       For Col = 1 To 6 Step 5 '2 columns         ItemName = Sheets("SurveyData").Cells(1, i + 2)         With Cells(Row, Col)             .Value = ItemName             .Font.Size = 16         End With '   Create pivot table     Set PT = ActiveSheet.PivotTables.Add( _       PivotCache:=PTCache, _       TableDestination:=SummarySheet.Cells(Row + 1, Col)) '   Add the fields     If Col = 1 Then 'Frequency tables         With PT.PivotFields(ItemName)             .Orientation = xlDataField             .Name = "Frequency"             .Function = xlCount         End With     Else ' Percent tables     With PT.PivotFields(ItemName)         .Orientation = xlDataField         .Name = "Percent"         .Function = xlCount         .Calculation = xlPercentOfColumn         .NumberFormat = "0.0%"     End With     End If     PT.PivotFields(ItemName).Orientation = xlRowField     PT.PivotFields("Sex").Orientation = xlColumnField     PT.TableStyle2 = "PivotStyleMedium2"     PT.DisplayFieldCaptions = False     If Col = 6 Then '       add data bars to the last column         PT.ColumnGrand = False PT.DataBodyRange.Columns(3).FormatConditions. _     AddDatabar     End If Next Col          Row = Row + 10     Next i '   Replace numbers with descriptive text     With Range("A:A,F:F")         .Replace "1", "Strongly Disagree"         .Replace "2", "Disagree"         .Replace "3", "Undecided"         .Replace "4", "Agree"         .Replace "5", "Strongly Agree"     End With End Sub End Sub 

Notice that all of these pivot tables were created from a single PivotCache object.

The pivot tables are created within a nested loop. The Col loop counter goes from 1 to 6 by using the Step parameter. The instructions vary a bit for the second column of pivot tables. Specifically, the pivot tables in the second column, do the following:

• Display the count as a percent of the column.

• Do not show grand totals for the rows.

• Are assigned a number format.

• Display format conditioning data bars.

The Row variable keeps track of the starting row of each pivot table. The final step is to replace the numeric categories in columns A and F with text. For example, 1 is replaced with Strongly Agree .