Code problems for pivot table

C

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
 
M

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top