A quick paste of what I wrote a while ago...
maybe not be entirely geared to what you need, but
the PivotsLayout does contain what you're asking.


Sub PivotsCreate()
With ActiveWorkbook
On Error Resume Next
.Names("dnPivSource").Delete
With Worksheets("Pivots")
.PivotTables("Pivot1").TableRange2.Clear
.PivotTables("Pivot2").TableRange2.Clear
End With
On Error GoTo 0
.Names.Add "dnPivSource", _
"=OFFSET(ReadbyAdo!$A$1,0,0,COUNTA(ReadByAdo!$A:$A ),COUNTA
(ReadByAdo!$1:$1))"
With .PivotCaches.Add(xlDatabase, "dnPivSource")
.CreatePivotTable [Pivots!A3], "Pivot1"
.CreatePivotTable [Pivots!Z3], "Pivot2"
End With
End With
End Sub

Sub PivotsLayout()
Dim heads As Variant
Dim pt As PivotTable
Dim pf As PivotField

heads = [dnPivSource].Resize(1)
For Each pt In Worksheets("Pivots").PivotTables
With pt
For Each pf In .VisibleFields
pf.Orientation = xlHidden
Next
.AddFields Array(heads(1, 4), heads(1, 5), heads(1, 1)), _
Array(heads(1, 2)), _
Array(heads(1, 3))
If pt.Name = "Pivot1" Then
.AddDataField .PivotFields(heads(1, 6)), "Top5", xlSum
With .PivotFields(heads(1, 4))
.AutoShow xlAutomatic, xlTop, 5, "Top5"
.AutoSort xlDescending, "Top5"
End With
Else
.AddDataField .PivotFields(heads(1, 6)), "Bot5", xlSum
With .PivotFields(heads(1, 4))
.AutoShow xlAutomatic, xlBottom, 5, "Bot5"
.AutoSort xlAscending, "Bot5"
End With
End If
End With
Next
End Sub

Sub PivotsResetSource()
Dim pt As PivotTable
Debug.Print "Changing Source, notice no update event"
ActiveWorkbook.Names.Add "dnPivSource", _
"=OFFSET(AltData4Pivot!$A$1,0,0,COUNTA(AltData4Piv ot!$A:$A),COUNTA
(AltData4Pivot!$1:$1))"
Debug.Print "Source Changed"
Debug.Print "Refresh cache, notice both tables are updated"
Worksheets("Pivots").PivotTables(1).PivotCache.Ref resh
End Sub



keepITcool

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


"Robert Stober" wrote:

Hi,

I'm progamming a pivot table using VBA. I have no problem manipulating
column or row fields, but how can I replace a data field? Everything I
try just adds the data field to the existing data field resulting in a
mess.

The PivotTable.addfields methods *replaces* whatever columns were
previously designated as RowFields, ColumnFields, and PageFields with
the new specifications, as in:

.AddFields RowFields:="Date", PageFields:="Location"
But no matter what I try, I can't replace the data field.... I've even
tried setting the Orientation property to xlHidden, as follows:

' Hide existing data fields
For each pf In .DataFields
Debug.Print "deleting pf", pf.Name
pf.Orientation = xlHidden
'pf.Delete ' I tried deleting too
Next

They key is that I need to do this via code. I can easily do it by
using the Excel GUI. I recorded the resulting code - Excel was hidding
the field by setting its Orientation property to xlHidden. Why doesn't
that work for me?

Many thanks in advance,

Robert Stober