Thread: Accessing data in pivot tables

Hi, I'm trying to access data sitting inside a pivot table. When I
record a macro, it uses the GETPIVOTDATA function.

ActiveCell.FormulaR1C1 = _
"=GETPIVOTDATA(""MR#"",R10C2,""Biopsy Year"",1993,""family
hx2"",""U"")/GETPIVOTDATA(""MR#"",R10C2,""Biopsy Year"",1993)"

Is there a more direct way to access the data without having to set the
value within another cell?

David

What are you trying to pull from the pivot table? There's also a
GetPivotData method that you can use, following the examples in Excel's
Help.

If you want to record a macro without using the GetPivotData function,
you can turn it off. There are instructions here:

http://www.contextures.com/xlPivot06.html

davidhwang@usa.com wrote:
> Hi, I'm trying to access data sitting inside a pivot table. When I
> record a macro, it uses the GETPIVOTDATA function.
>
> ActiveCell.FormulaR1C1 = _
> "=GETPIVOTDATA(""MR#"",R10C2,""Biopsy Year"",1993,""family
> hx2"",""U"")/GETPIVOTDATA(""MR#"",R10C2,""Biopsy Year"",1993)"
>
> Is there a more direct way to access the data without having to set the
> value within another cell?
>
> David
>


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Finally got around to trying it. Thanks for the help. I wasn't able
to see that option because my Excel Help is messed up.

Another questions: Is there a way to see all the values for a certain
field? For the example I show below, I'm using a biopsy year of 1993,
but is there a way to find out all the years that exist in the "biopsy
year" field?

Debra Dalgleish wrote:
> What are you trying to pull from the pivot table? There's also a
> GetPivotData method that you can use, following the examples in Excel's
> Help.
>
> If you want to record a macro without using the GetPivotData function,
> you can turn it off. There are instructions here:
>
> http://www.contextures.com/xlPivot06.html
>
> davidhwang@usa.com wrote:
> > Hi, I'm trying to access data sitting inside a pivot table. When I
> > record a macro, it uses the GETPIVOTDATA function.
> >
> > ActiveCell.FormulaR1C1 = _
> > "=GETPIVOTDATA(""MR#"",R10C2,""Biopsy Year"",1993,""family
> > hx2"",""U"")/GETPIVOTDATA(""MR#"",R10C2,""Biopsy Year"",1993)"
> >
> > Is there a more direct way to access the data without having to set the
> > value within another cell?
> >
> > David
> >
>
>
> --
> Debra Dalgleish
> Contextures
> http://www.contextures.com/tiptech.html