GETPIVOTDATA - Pivot table name problem?
 

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


--
Kobayashi
------------------------------------------------------------------------
Kobayashi's Profile: http://www.excelforum.com/member.php...nfo&userid=871
View this thread: http://www.excelforum.com/showthread...hreadid=276231

Syntax

GETPIVOTDATA(data_field,pivot_table,field1,item1,f ield2,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:=xlDatab ase, SourceData:= _
rng).CreatePivotTable TableDestination:="", _
TableName:="SummaryPivot"
Pivottables("SummaryPivot").TableRange1.Cells(1).N ame = "PT1anchor"



keepITcool

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


Kobayashi wrote in message
:


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

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