Format Pivot total rows

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
 
P

Patrick Molloy

did you try

..Format xlReport3

Liz said:
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()

Dim pt As PivotTable
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

SET pt = ActiveSheet.PivotTables("PivotTable1")

With pt
With .PivotFields("Status")
.Orientation = xlPageField
.Position = 1
End With

.PivotFields("Status").CurrentPage = "Published"

With .PivotFields("Category")
.Orientation = xlRowField
.Position = 1
End With

With .PivotFields("Product")
.Orientation = xlRowField
.Position = 2
End With

With .PivotFields("Industry")
.Orientation = xlColumnField
.Position = 1
End With

.Format xlReport3
.AddDataField

.PivotFields("Rating"), "Count of Rating", xlCount

end with

End Sub
 
P

Patrick Molloy

i also made some minor changes ...

Patrick Molloy said:
did you try

.Format xlReport3



Dim pt As PivotTable


SET pt = ActiveSheet.PivotTables("PivotTable1")

With pt
With .PivotFields("Status")
.Orientation = xlPageField
.Position = 1
End With

.PivotFields("Status").CurrentPage = "Published"

With .PivotFields("Category")
.Orientation = xlRowField
.Position = 1
End With

With .PivotFields("Product")
.Orientation = xlRowField
.Position = 2
End With

With .PivotFields("Industry")
.Orientation = xlColumnField
.Position = 1
End With

.Format xlReport3
.AddDataField

.PivotFields("Rating"), "Count of Rating", xlCount

end with

End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top