GETPIVOTDATA - Pivot table name problem?

[Kobayashi]

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 us
the following formula:

=getpivotdata(SummaryPivot, "TEST")

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

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

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



Thanks,

Adria

 

[keepITcool]

Syntax

GETPIVOTDATA(data_field,pivot_table,field1,item1,field2,item2,...)

Data_field is the name, enclosed in quotation marks, for the data
field that contains the data you want to retrieve.

Pivot_table is a reference to any cell, range of cells, or named range
of cells in a PivotTable report. This information is used to determine
which PivotTable report contains the data you want to retrieve.


first you'll need to reverse the arguments..

second you need to reference any cell in the pivot..

ActiveCell.Formula = "=getpivotdata(""test""," & _
ActiveSheet.PivotTables("SummaryPivot"). _
TableRange1.Cells(1).Address & ")"

you could simplyfy that by setting a range variable when you
create the PT.. or name the first cell.

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
rng).CreatePivotTable TableDestination:="", _
TableName:="SummaryPivot"
Pivottables("SummaryPivot").TableRange1.Cells(1).Name = "PT1anchor"



keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >


Kobayashi wrote in message

 

[Debra Dalgleish]

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"")"
'========================