Return value from a pivot table

[Joseph K. Young]

I have a pivot table that looks something like this:

Product1 Product2 Product3
Week1 2500 500 0
Week2 200 35 800
Week3 150 5000 3

Where the numbers are quantities of their corresponding product required by
week. In my current code I have variables that contain the column name and
the row name (i.e. varProduct and varWeek). How do I get the value of the
quantity that corresponds to the variables?

Thanks!

 

[Bill Manville]

In my current code I have variables that contain the column name and
the row name (i.e. varProduct and varWeek). How do I get the value of the
quantity that corresponds to the variables?

One way would be to use the worksheet GETPIVOTDATA function.

V = Application.Evaluate("GETPIVOTDATA(Sheet4!A1,""" & varProduct & " " &
varWeek & """)")

where you would replace Sheet4!A1 with the address (or range name) of a cell
in your pivot table.

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - reply in newsgroup