Find function > Pivot Table

[dinadvani via OfficeKB.com]

Hello,

I am having some problem with the find function.

I have a template and a report. On the basis of this report i hve prepared a
pivot table. Now what i need is to search the pivot table and if value is
found then paste the value frm pivot to template.

I am trying to search using find function > for instance search Jan in pivot
table > if found then pick the next cell which is the total value for jan and
paste that in template.....i m trying to do this for all months but there can
be an instance where month is not there in that case i want to paste a zero
against tht month. (for eg..if feb is not found paste "0" against feb in
template)

But i am unable to get the results which i want. I tried using
Set r = Cells.Find(What:="Jan-08", LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:
=False _
, SearchFormat:=False).Activate
If Not r Is Nothing Then
ActiveCell.Offset(0, 1).Select
ActiveCell.Copy
val = activecell.value
ActiveWindow.ActivateNext
Range("d13").Select
ActiveCell.Value = val
Else
ActiveWindow.ActivateNext
Range("d13").Select
ActiveCell.Value = 0

Please help

Thanks,
DA

 

[Bernie Deitrick]

Use the GETPIVOTDATA function. Select a cell, type =, then select the subtotal value that you want
from the Pivot Table. You'll get a function that looks like

=GETPIVOTDATA("Sum of Value",PivotSheet!$A$3,"Date",DATE(2008,1,1))

and it will update properly as the pivot table is changed.

HTH,
Bernie
MS Excel MVP