This snippet works in xl97 AND xlXP (sorry can't test xl2k)
it's as flexible as possible as it avoids "hardcoding"...

datafields are added last to avoid maximum size..

you'll need sheets "data" and "pivots"


ActiveWorkbook.Names.Add "dnPivSource", _
"=OFFSET(Data!$A$1,0,0,COUNTA(Data!$A:$A),COUNTA(D ata!$1:$1))"

heads = [dnPivSource].Resize(1)

On Error Resume Next
Worksheets("Pivots").PivotTables("Pivot1").TableRa nge2.Clear
On Error GoTo 0

Set pt = ActiveWorkbook.PivotCaches.Add(xlDatabase, _
"dnPivSource").CreatePivotTable([Pivots!A1], "Pivot1")
With pt
For Each pf In .VisibleFields
pf.Orientation = xlHidden
Next
.AddFields _
RowFields:=Array(heads(1, 1), heads(1, 2)), _
ColumnFields:=Array(heads(1, 3)), _
PageFields:=Array(heads(1, 4))
.PivotFields(heads(1, 5)).Orientation = xlDataField
.DataFields(1).Function = xlSum
.DataFields(1).Name = "Top5"
.RowFields(1).AutoSort xlDescending, .DataFields(1).Name
.RowFields(1).AutoShow xlAutomatic, xlTop, 5,.DataFields(1).Name
.RowFields(1).Subtotals(1) = False
End With


keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


DaveGulliksen wrote:

I, too, have been struggling with the same problem in Excel 2000.

I've recorded a macro where I've dragged a field into the DataField
area and pulled it back out again to hide it, but when I try to play
the macro back, it fails with the message "Unable to set the
Orientation property of the PivotField class". Here's the code:

Sub Macro8()

With ActiveSheet.PivotTables("PivotTable1").PivotFields ("EndBal")
Orientation = xlDataField
Position = 1
End With

ActiveSheet.PivotTables("PivotTable1").PivotFields ("Count of
EndBal").Orientation = xlHidden <====== FAILS HERE (also fails with
"EndBal" instead of "Count of EndBal")

End Sub

So far, the only workaround that seems to do the job is to delete the
cell with the fieldname in it: Range("A4").Delete

Of course, you have to figure out which cell has the field header in
it
in order to delete it. Have you found any other way to do it?

Dave G



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from
http://www.ExcelForum.com/