Update pivot using drop-down cell value stored on separate worksheet

Hello.

- I have a drop down cell (C2) on 'Sheet1' which contains a series of fixed values.
- I have a pivot table on 'Sheet2' called 'TestPiv' with a field 'Test' that corresponds to the value selected in Sheet1 cell C2.
- I would like the pivot table on Sheet2 to automatically be filtered based upon the value currently selected on Sheet1 cell C2.

Googling gave me the below, which I've tried to manipulate to my needs but haven't been successful. I've tried:

(a) adding the Sheet1 worksheet to the Intersect(Target... but learned that the target cell must be on the same sheet.
(b) creating a "helper" cell in cell A1 of Sheet 2 which references cell C2 of Sheet 1, but the pivot only updates if I physically type the value into A1.

Any help greatly appreciated!

Please Login or Register  to view this content.

Is the drop down cell that is changing on Sheet1 from cell validation or is it actually a drop down combo box you added using the tools on the developer tab?

If it is from cell validation there are a couple of issues to consider:
1. You have to have the code you show above on Sheet1 where the cell value is changing. Do NOT use the helper cell but make sure the If Intersect line is referencing your changing cell on C2.
2. This code only works if the user is typing in the change. If the user is clicking with the mouse then the code needs to be in the OnClick event.

If it is a combo box then reference the combo box rather than the cell. You also should put your code in the combo box change event rather than the worksheet change event.