Hi,
I'm trying to use VBA to pull data from a Pivot Table and keep getting errors

Below is a simple example:
My Pivot Table Sheet Name defined in VBA is "PivOpp"
The Pivot Table on the sheet is named: "Opps Pivot"
I have a field called "Confidence Sales Calculation" & "Sourcing Year"
'Confidence Sales Calculation' is a field which I want summed up in a total.
The Field 'Sourcing Year' has a valid item called "2009".
I want to get a sum of the 'Confidence Sales Calculation' for 2009.

I keep getting runtime error 1004 with both of the below examples.
Sub MyTest is how I tried using GetData
Sub UseGetPivotData is straight out of the help file just modifing the fields.
I'm not sure what the problem is and why methods work.

Thanks for any help!


Sub MyTest
dim test2
test2 = PivOpp.PivotTables("Opps Pivot").GetData("'Sum of Confidence
Sales' Calculation' Sourcing Year 2009")
End Sub

Sub UseGetPivotData()

Dim rngTableItem As Range

' Get PivotData for the quantity of chairs in the warehouse.
Set rngTableItem = ActiveCell. _
PivotTable.GetPivotData("Confidence Sales Calculation", "Sourcing
Year", "2009")

MsgBox "The quantity of chairs in the warehouse is: " & rngTableItem.Value

End Sub

Let me elaborate....
The pivot table doesn't have all of these fields in the table itself...
Sourcing Year is actually above the table as a select box.
Maybe a better way to say it is that:

I want to pull data from the pivot table range without the limitations of
how the pivot table currently is structured...
Not sure if that makes sense or not.

Thanks, MikeZz

"MikeZz" wrote:

Hi,
I'm trying to use VBA to pull data from a Pivot Table and keep getting errors

Below is a simple example:
My Pivot Table Sheet Name defined in VBA is "PivOpp"
The Pivot Table on the sheet is named: "Opps Pivot"
I have a field called "Confidence Sales Calculation" & "Sourcing Year"
'Confidence Sales Calculation' is a field which I want summed up in a total.
The Field 'Sourcing Year' has a valid item called "2009".
I want to get a sum of the 'Confidence Sales Calculation' for 2009.

I keep getting runtime error 1004 with both of the below examples.
Sub MyTest is how I tried using GetData
Sub UseGetPivotData is straight out of the help file just modifing the fields.
I'm not sure what the problem is and why methods work.

Thanks for any help!


Sub MyTest
dim test2
test2 = PivOpp.PivotTables("Opps Pivot").GetData("'Sum of Confidence
Sales' Calculation' Sourcing Year 2009")
End Sub

Sub UseGetPivotData()

Dim rngTableItem As Range

' Get PivotData for the quantity of chairs in the warehouse.
Set rngTableItem = ActiveCell. _
PivotTable.GetPivotData("Confidence Sales Calculation", "Sourcing
Year", "2009")

MsgBox "The quantity of chairs in the warehouse is: " & rngTableItem.Value

End Sub