Update Pivot page field from a cell entry

S

Santhosh Mani

Please find below a code which I recieved from this discussion forum and used
with Excel 2003. This code is used to update a pivot page field by entering
required value in a particular cell (Cell F4 in the below code) and double
click on it. This code is working perfect with Excel 2003 but when I convert
the same file into Excel 2007 and try to use it, I get debug screen pointed
to the line - SubNum.CurrentPage = Pi.Value.

Can someone help with this issue and make this code work under Excel 2007.
Thanks in advance.. Santhosh
--------------------------------------------
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)

Dim SubNum As PivotField

If Target.Count > 1 Then Exit Sub
If IsEmpty(Target.Value) Then Exit Sub
If Target.Address = "$F$4" Then
Worksheets("StmtData").PivotTables("PivotTable1").PivotCache.Refresh
Set SubNum = Worksheets("StmtData").PivotTables _
("PivotTable1").PageFields( _
"SubNo")

For Each Pi In SubNum.PivotItems
If Pi.Value = Target.Text Then
SubNum.CurrentPage = Pi.Value
Exit For
End If
Next
End If

Worksheets("StmtUSD").PivotTables("PivotTable2").PivotCache.Refresh


End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top