Using GetPivotData in VBA

[cstimart]

I'm trying to use this worksheetfunction in a Macro w/no luck...any ideas?
(I initially recorded a macro to get the correct "GetPivotData" elements and altered)

here is the partial/relevant code

Dim Temp as Variant
.
.
For i = 6 To LR
    If Range("C" & i) <> "" Then
        Temp = WorksheetFunction.GetPivotData("Counts", Sheets(2).Range("A7"), "Year Resolved", Range("B" & i), "Year UW", Range("C" & i))
            If Not IsNumeric(Temp) Then
                Range("E" & i) = 0
            Else
                Range("E" & i) = Temp
            End If
    End If
Next i

My code stops at the Temp = line...and says "Object doesn't support this property or method"

 

[Andrew Poulsom]

In VBA GetPivotData is a member of the PivotTable object (not of the WorksheetFunction object).

 

[cstimart]

In VBA GetPivotData is a member of the PivotTable object (not of the WorksheetFunction object).

I changed to...

Temp = PivotTable.GetPivotData...

..and I get "Object Required"

 

[Andrew Poulsom]

You need to apply it to your pivottable, eg ActiveSheet.PivotTables(1). Have a look at the example in VBA Help.

 

[cstimart]

From looking at the Help, I added/changed the following...

Dim PT As PivotTable
    Set PT = Sheets("Access Data").PivotTables("Access_Pivot")
.
.
.
Temp = PT.GetPivotData("Counts", "Year Resolved", Sheets("Report").Range("B" & i), "Year UW", Sheets("Report").Range("C" & i))

It works, until I hit an instance were the Values in B & C don't apply. I was hoping that the following code would help me fill a zero, but the code does not fill "Temp" with an error.

            If Not IsNumeric(Temp) Then
                Range("E" & i) = 0
            Else
                Range("E" & i) = Temp

 

[cstimart]

...or is it possible to change the pivot table to show all the parameters, even if the results are zero?

 

[Andrew Poulsom]

What is assigned to Temp when the values in B & C don't apply?

 

[cstimart]

What is assigned to Temp when the values in B & C don't apply?

I guess "Don't Apply" isn't the right term.

Example:

Sheet1, Column A contains years 1999 - 2011
Sheet2, pivot table data: depending on the filter(s), some years will have no applicable data and those years do not appear in the Pivot.

Then when going thru the code and trying to look for a non-existent year in the pivot table, the code crashes with the Application-defined or object-defined error.

 

[Andrew Poulsom]

You need an error trap:

    Dim PT As PivotTable
    Set PT = Sheets("Access Data").PivotTables("Access_Pivot")
'   Start of loop
    On Error Resume Next
    Temp = PT.GetPivotData("Counts", "Year Resolved", Sheets("Report").Range("B" & i), "Year UW", Sheets("Report").Range("C" & i))
    If Err = 0 Then
        Range("E" & i) = Temp
    Else
        Err.Clear
        Range("E" & i) = Temp
    End If
    On Error GoTo 0
'   End of loop

 

[cstimart]

You need an error trap:

    Dim PT As PivotTable
    Set PT = Sheets("Access Data").PivotTables("Access_Pivot")
'   Start of loop
    On Error Resume Next
    Temp = PT.GetPivotData("Counts", "Year Resolved", Sheets("Report").Range("B" & i), "Year UW", Sheets("Report").Range("C" & i))
    If Err = 0 Then
        Range("E" & i) = Temp
    Else
        Err.Clear
        Range("E" & i) = 0
    End If
    On Error GoTo 0
'   End of loop

Thanks!

I did have to make 1 slight change.