How to trigger an event depending on a cell changing as a result of a formula

[stakar]

I have the following problem

Im using a pivot table and i change the value of the pivot table page

This pivot table's value updates a cell.
So i want each time the specific cell is updating with the pivo
table's value to update another pivot table.

Be more specific.

The cell's value depends on pivot table's cell value (B2) by using th
formula (=B2) in the cell F14.

So, if the pivot table = 1 then the cell's value will be 1
if the pivot table = 12 then the cell's value will be 12

I want to trigger an event each time the cell's value changes so t
update another table.

I found that code but its not running and it doesnt call the routin
'ResfreshWrc'

------------------------------------------------------------
Dim MonitorCell As Variant

Private Sub Worksheet_Activate()
MonitorCell = Cells(14, 6)
End Sub

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Cells(14, 6) <> MonitorCell Then
RefreshWrc
MonitorCell = Cells(14, 6)
End If
End Sub

-----under Module---------------------------------------

Sub RefreshWrc()
ActiveSheet.PivotTables("WRC").RefreshTable
End Sub



****************************************
Thanks in Advance
Stathis
Patras, Greec

 

[Bernie Deitrick]

stakar,

Try using the calculate event instead, and using a cell as the storage of
the old value:

Dim MonitorCell As Range

Private Sub Worksheet_Calculate()
Set MonitorCell = Cells(15,6) ' use the cell F15 as a recording cell
If Cells(14, 6).Value <> MonitorCell.Value Then
RefreshWrc
MonitorCell.Value = Cells(14, 6).Value
End If
End Sub

HTH,
Bernie
MS Excel MVP

 

[stakar]

Bernie,
Thanks for the replay but there's a problem

When the coursor is on the 'RefreshWrc' refreshes the table 30 times a
least, consecutively.
It works like the pivot table's refresh button stucks and refreshin
the table consecutively.

Thanks in advance

***************
Stathi

 

[Bernie Deitrick]

Stathis,

You need to disable events when you do something that may force another
calculation. Try this version:

Private Sub Worksheet_Calculate()
Set MonitorCell = Cells(15,6) ' use the cell F15 as a recording cell
If Cells(14, 6).Value <> MonitorCell.Value Then
Application.EnableEvents = False
RefreshWrc
Application.EnableEvents = True
MonitorCell.Value = Cells(14, 6).Value
End If
End Sub

HTH,
Bernie
MS Excel MVP

 

[stakar]

*Stathis,

Try changing

ActiveSheet.P.....

to

Worksheets("SheetNameWithThePivotTable").P....

HTH,
Bernie
MS Excel MVP

Bernie,
It doesnt work

The main concept is how to refresh a pivot table which is in
worksheet "B" when i am running a vb code which using the workshhe
"A".

Thanks
****************
Stathis
Patras- Greec

 

[Bernie Deitrick]

Stathis,

We need to clear up terminology. When you say worksheets, do you mean a
separate workbook (with a different file name), or a separate tab
(worksheet) within a single workbook?

Bernie

 

[stakar]

Bernie,
Saying workbook i mean the xls file eg. c:\myname.xls
Saying worksheets i mean the tabs, the sheets in a single workbook

 

[Bernie Deitrick]

stakar,

Try

Worksheets("SheetNameWithThePivotTable").Activate

ActiveSheet.P.....

HTH,
Bernie
MS Excel MVP