I am trying to use Worksheet_Change event and having a few issues with it.

I have two pivots in a sheet, both pivots have the same source but hold
different data, what I am trying to do is if I change the page field of one
pivot then the page field of the second pivot should change automatically. I
succeded in doing that, what I did was I created a link to first pivots page
field in cell 'A9' and used the following code which updates the second pivot:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim mystr As String

If Intersect(Target, Range("a9")) Is Nothing Then

mystr = Range("a9").Value
ActiveSheet.PivotTables("PivotTable2").PivotFields("D").CurrentPage = mystr

Else
End If

End Sub


The issue which I am facing is, the macro just keeps on refreshing the sheet on
and on. I guess it is because of Worksheet_Change event which runs with change
in each cell.

So I need help to figure out how can I :

1- Trigger a macro when value of a cell changes (part of which I have done)
2- Make this code more robust so that it quicly refreshes the pivot and does not
go on and on.
3- If any one could suggest me to use a different piece of code which would be
more efficient.