L
Liz
Hi -
I am using the following code to automatically create a pivot table. I
would like to add formatting to the pivot table, especially the auto
generated Total rows and Grand Total row. How can I do that?
Any other formatting tips to make this report better looking is also
appreciated. This is Excel 2003. Would Excel 2007 add much more formatting
capabilities for Pivots via VBA?
Thanks,
Liz
Private Sub Pivot_Report()
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
Sheets("Data Log").Range("A1").CurrentRegion.Offset(1, 0)).CreatePivotTable _
TableDestination:="", TableName:="PivotTable1", _
DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveWorkbook.ShowPivotTableFieldList = True
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Status")
.Orientation = xlPageField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").PivotFields("Status"). _
CurrentPage = "Published"
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Category")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Product")
.Orientation = xlRowField
.Position = 2
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Industry")
.Orientation = xlColumnField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField
ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Rating"), "Count of Rating", _
xlCount
End Sub
I am using the following code to automatically create a pivot table. I
would like to add formatting to the pivot table, especially the auto
generated Total rows and Grand Total row. How can I do that?
Any other formatting tips to make this report better looking is also
appreciated. This is Excel 2003. Would Excel 2007 add much more formatting
capabilities for Pivots via VBA?
Thanks,
Liz
Private Sub Pivot_Report()
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
Sheets("Data Log").Range("A1").CurrentRegion.Offset(1, 0)).CreatePivotTable _
TableDestination:="", TableName:="PivotTable1", _
DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveWorkbook.ShowPivotTableFieldList = True
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Status")
.Orientation = xlPageField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").PivotFields("Status"). _
CurrentPage = "Published"
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Category")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Product")
.Orientation = xlRowField
.Position = 2
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Industry")
.Orientation = xlColumnField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField
ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Rating"), "Count of Rating", _
xlCount
End Sub