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 |
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 |
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 |
All times are GMT +1. The time now is 06:03 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com