Update Pivotfilter against Cell value

[Henrik]

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

 

[Patrick Molloy]

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("region").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]

Thanks for quick response.

Do you have an suggestion to a solution on the following scenarie as well -
as after considering the solution rather would go for a solution which can
solve the following scenarie:

1)Sheet 1 with Pivottable1 is connected to Sales OLAP db which contains a
lot of data including an item numbers

2)When the user stand on one cell in the Item Number column in the
pivottable1 - containing the item number - let's say for example "100-200500"
- and then click a button

3)Then the Pivottable2 on sheet2 connected to the Production OLAP db get
filtered on the item that's selected(cell is active) on sheet1

4) if the item does not exist in the production olap a error message is giving

Does it makes sense - and do you have a suggestion for this scenario

Thanks again for your help
Henrik

 

[Patrick Molloy]

it sounds perfectly reasonable

I'd suggest a double click for the cell in pivottable1

your code should checks that the cell thats double clicked is in the
correcte column, and then you can push that value to pivottable2

 

[Henrik]

Do you have a code suggestion how to solve this ?

Thanks
Henrik