depending on what you're changing .... you can use the sheet's change event.

example
pivot table is on sheet2 and its a sales / marketing pivot. The page field
is Region that i want to filter on

on sheet1, range A1 is where i place my state code, like MA or OR
the code page for sheet1 I add this:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Range("A1").Address Then
Sheet2.PivotTables("PivotTable1").PivotFields("reg ion").CurrentPage
= Range("A1").Value
Sheet2.Activate
End If
End Sub


so when I type a state code into A1 of sheet1, the event fires, the code
checks which cell was changed, and if its A1, the value is placed into the
page field to change the region filter, then the sheet holding the pivot is
activated

is this the kind of idea that you want?


"Henrik" wrote in message
...
Hello Experts,
I have today 2 sheets - and I would like to create the following:

When the focus is on one cell in sheet 1 - I would like to have a button
which the user can click (a macro behind)to insert that value into a pivot
filter on sheet 2 - and then refresh the pivottable on sheet 2 based on
the
new filter

How can I do that....?

Any help is appriciated
Thanks
Henrik