Code problems for pivot table

[Chris Freeman]

Hi there,
I created a pivot table and captured the keystrokes as a recorded macro. But
now when I re-run the code, I get error message 1004: Unable to get the
PivotItems property of the PivotField class. the lspecific line that fails:
ActiveSheet.PivotTables("PivotTable1").DataPivotField.PivotItems("Sum of
Value").Position = 1

The problem results in crerating the pivot tables, but the coulms come out
with a Count of Values instead of Sum of Values.

Here's the entire code:
Range("A1:B1").Select
Range(Selection, Selection.End(xlDown)).Select
'create pt for trust acct
ActiveWorkbook.PivotCaches.Add(SourceType:=xlConsolidation,
SourceData:= _
Array("Sheet1!R1C1:R76C2", "Item1")).CreatePivotTable
TableDestination:= _
"'[Funds Ops Recon 06232009.xls]Sheet1'!R16C10",
TableName:="PivotTable1", _
DefaultVersion:=xlPivotTableVersion10

ActiveSheet.PivotTables("PivotTable1").DataPivotField.PivotItems("Sum of
Value").Position = 1 '<<<<< this line is
where it fails
ActiveWorkbook.ShowPivotTableFieldList = True
Application.CommandBars("PivotTable").Visible = False
ActiveWorkbook.ShowPivotTableFieldList = False

Thanks in advance for any tips

 

[marcus]

Hi Chris

I think the problem with your code is you are trying to move the 'Sum
of Value'. The pivot item is 'Value'. If you change the code
slightly so you move the value field, so;

With ActiveSheet.PivotTables("PivotTable1").PivotFields("Value")
.Position = 1
End With

This should work...... Hope it helps.


Take care

Marcus