About Us

Patrick Molloy

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

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Format Total but not GRAND Total rows	MikeF[_2_]	Excel Programming	0	December 29th 08 11:04 PM
Format Total but not Grand Total rows	MikeF[_2_]	Excel Programming	0	December 29th 08 11:01 PM
Pivot Table Conditonal Format of the Grand Total	michelle	Excel Worksheet Functions	3	November 2nd 06 11:39 PM
Pivot Table - Grand Total for Rows	Joe	Excel Discussion (Misc queries)	3	May 31st 06 12:06 AM
Display format for Total Column of Pivot Table	PP	Excel Programming	1	July 29th 05 05:31 AM

Menu

About Us