Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format Pivot total rows
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:=xlDatab ase, 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").AddDataFiel d ActiveSheet.PivotTables( _ "PivotTable1").PivotFields("Rating"), "Count of Rating", _ xlCount End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format Pivot total rows
did you try ..Format xlReport3 "Liz" wrote in message ... 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:=xlDatab ase, 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format Pivot total rows
i also made some minor changes ...
"Patrick Molloy" wrote in message ... did you try .Format xlReport3 "Liz" wrote in message ... 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:=xlDatab ase, 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Format Total but not GRAND Total rows | Excel Programming | |||
Format Total but not Grand Total rows | Excel Programming | |||
Pivot Table Conditonal Format of the Grand Total | Excel Worksheet Functions | |||
Pivot Table - Grand Total for Rows | Excel Discussion (Misc queries) | |||
Display format for Total Column of Pivot Table | Excel Programming |