Microsoft Office Tutorials and References
In Depth Information
Chapter 17: Working with Pivot Tables
h Month: A column field in the pivot table.
h Sales: A values field in the pivot table that uses the Sum function.
I turned on the macro recorder before I created this pivot table and specified a new worksheet
for the pivot table location. The code that was generated follows:
Sub RecordedMacro()
Range(“A1”).Select
Sheets.Add
ActiveWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, _
SourceData:=”Sheet1!R1C1:R13C4”, _
Version:=xlPivotTableVersion14).CreatePivotTable _
TableDestination:=”Sheet2!R3C1”, _
TableName:=”PivotTable1”, _
DefaultVersion:=xlPivotTableVersion14)
Sheets(“Sheet2”).Select
Cells(3, 1).Select
With ActiveSheet.PivotTables(“PivotTable1”) _
.PivotFields(“SalesRep”)
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables(“PivotTable1”) _
.PivotFields(“Month”)
.Orientation = xlColumnField
.Position = 1
End With
ActiveSheet.PivotTables(“PivotTable1”) _
.AddDataField ActiveSheet.PivotTables(“PivotTable1”) _
.PivotFields(“Sales”), “Sum of Sales”, xlSum
With ActiveSheet.PivotTables(“PivotTable1”). _
PivotFields(“Region”)
.Orientation = xlPageField
.Position = 1
End With
End Sub
If you execute this macro, it will almost certainly produce an error. Examine the code, and you’ll
see that the macro recorder hard-coded the worksheet name ( Sheet2 ) for the pivot table. If
that sheet already exists (or if the new sheet that’s added has a different name), the macro ends
with an error. It also hard-coded the pivot table name. The name won’t be PivotTable1 if the
workbook has other pivot tables.
But even though the recorded macro doesn’t work, it’s not completely useless. The code
provides lots of insight for writing code to generate pivot tables.
 
Search JabSto ::




Custom Search