Return value from a pivot table

  • Thread starter Thread starter Joseph K. Young
  • Start date Start date
J

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!
 
Joseph said:
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?
Click to expand...

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
 
Back
Top