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).

Mure ICH.itiDii^ jrr çaivunienL

Mart IciLjEiutu .i'm cuivCTiirnL

Mure ICH.itiDii^ jrr çaivunienL

Mart IciLjEiutu .i'm cuivCTiirnL

Ii.

Lb i-rrv

iTlf^' Z U^fl

a

»3

m

'r.ic-'-j r Z U£r*i

ihlh

f&wHC

L> >«{ IT

K

A

tMtii

ïti'-

LtatefM

u

1

»

» A -j

ÎI.Dï

ii.ri ■

3» m

1

M

ifc\*

37 7% IK

un

a

_2

j'JU^i V^P*«

i1*

D.otS ï

UM

CniFrj

19

H

LU

Mon hour» jr* cwnenwH

ut#

chfjjitam

h^tfÎHfH

LH"-

PHfll

Y.Sh

13.1*

m_

31CN

indKidnd

*,-<>■

IMM

i+rm.

Ît?1»

KlDWlpM

UH

UK

Stofei -ifiC rt^lHirsiini» rwd

IpiwiiiH

1

rennte

ljj-u l(Hl

S "j & wpTi

».i*

:in

Ek-^r.

M*

■ _

ÜH

ILïÛ

H,M

k

«jh

äft

ni»

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)

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

' 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 .

0 0

Post a comment