extract data from pivotfield not working

[werafa]

Hi all

I am trying to use VBA to extract data from a pivot table.

first, I have extracted the relevant pivot fields as objects
[vba]Set myPropName = myPivot.RowFields("Property Name")
Set myConsumption = myPivot.DataFields(1)
Set myCost = myPivot.DataFields(2)
Set myEst = myPivot.DataFields(3)[/vba]
then I have set some target ranges
[vba]numRows = mySheet.Range("H4").Value
Set rankRange = mySheet.Range(Cells(11, 1), Cells(10 + numRows, 1))
Set nameRange = mySheet.Range(Cells(11, 2), Cells(10 + numRows, 2))
Set costRange = mySheet.Range(Cells(11, 3), Cells(10 + numRows, 3))
Set consumptionRange = mySheet.Range(Cells(11, 4), Cells(10 + numRows, 4))
Set estRange = mySheet.Range(Cells(11, 6), Cells(10 + numRows, 6))[/vba]
then I try to loop through the objects and extract the required data.
[vba] For myRow = 1 To numRows
rankRange(myRow).Value = myRow
nameRange(myRow).Value = myPropName.DataRange.Cells(myRow).Value2 'this works
costRange(myRow).Value = myCost.DataRange.Cells(myRow, 2).Value2 'this causes an object var. or with block not set error
consumptionRange(myRow).Value = myConsumption.DataRange.Cells(myRow, 2).Value2
estRange(myRow).Value = myEst.DataRange.Cells(myRow, 2).Value2
Next myRow[/vba]
Extracting the value from 'myPropname' works, but the code breaks on the next line. this datafield has two columns, and the locals window shows 'Value2(1,2) as the name of the value I wish to read (hence my reference to datarange.cells). can anyone show me the correct syntax?

for reference, the pivot table is built with property names as row labels, and 3 column headings, each with two rows of data each (years 1 and 2). This is achieved by putting 'Fin Year' after 'Values' in the column labels box

for anyone else interested coding with pivot tables, http://peltiertech.com/WordPress/ref...ranges-in-vba/ has got me this far.

Thanks

[werafa]

The barstool of a thing just started working.....
must have been some sort of a compile glitch

oh well. If you can't join 'em, beat 'em.

Cheers all and happy new year