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

--
Message posted via http://www.officekb.com

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


"dinadvani via OfficeKB.com" <u22798@uwe wrote in message news:83738f4401adb@uwe...
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

--
Message posted via http://www.officekb.com