After you create the pivot table, you could build the GetPivotData
formula. Assuming that you're using Excel 2000, add code similar to the
following:

'=======================
Dim str As String
str = ActiveSheet.Name & "!" & _
ActiveSheet.PivotTables(1).TableRange2.Cells(1, 1).Address
Sheets("Lists").Range("G3").Formula = _
"=GETPIVOTDATA(" & str & ",""TEST"")"
'========================

Kobayashi wrote:
I have a pivot table that I create with code and name 'SummaryPivot'.
However, could somebody please explain why I get an error when I use
the following formula:

=getpivotdata(SummaryPivot, "TEST")

I think it's because the name does not reference an actual cell within
the PT, just the PT object itself? Why is this? What's the best way to
get around this? I'll be placing this function within a VBA procedure
so don't really want to have to define an additional name?

The following is my code that creates/names the pivot in the first
place, if it helps?

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
rng).CreatePivotTable TableDestination:="", _
TableName:="SummaryPivot"



Thanks,

Adrian




--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html