Listing 6-1.MakePivotTable Macro Code
Sub MakePivotTable()
'
' MakePivotTable Macro
'
'
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= ??
"Sheet1!R1C1:R43C6", Version:=xlPivotTableVersion12).CreatePivotTable ??
TableDestination:="Sheet4!R3C1", TableName:="PivotTable1", DefaultVersion ??
:=xlPivotTableVersion12
Sheets("Sheet4").Select
Cells(3, 1).Select
CHAPTER 6 n PIVOTTABLES 229
With ActiveSheet.PivotTables("PivotTable1").PivotFields("State")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Product")
.Orientation = xlColumnField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables(??
"PivotTable1").PivotFields("Qty"), "Sum of Qty", xlSum
With ActiveSheet.PivotTables("PivotTable1").PivotFields("City")
.Orientation = xlRowField
.Position = 2
End With
End Sub
The first thing the code does is add a new worksheet to the workbook. Then it creates the
PivotTable using the source data range we provided in the Create PivotTable dialog box. Then
it places the PivotTable on the new sheet (in this case Sheet4) and gives it a default name.
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= ??
"Sheet1!R1C1:R43C6", Version:=xlPivotTableVersion12).CreatePivotTable ??
TableDestination:="Sheet4!R3C1", TableName:="PivotTable1", DefaultVersion ??
:=xlPivotTableVersion12
The PivotCaches.
Pages:
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223