Update Pivot Report Filter Value based on cell updated by formula

In short, I would like the value of a Pivot report filter on one sheet to be the same as the value in cell B2 on another sheet which changes depending on the value of a VLOOKUP formula in B2.

I have been able to track down two different macros that do the job of updating the report filter value in a pivot table based on cell B2 updating.

The problem is that neither macro works automatically. You have to physically run the macro to get the report filter to update. I would like the report filter of the pivot table to update each time the value resulting from the VLOOKUP changes.

One of the macros that works, but fails to run automatically is below:

Please Login or Register  to view this content.

The other macro uses named ranges and goes something like:

Please Login or Register  to view this content.

Note that I have removed the named ranges from the file attached to avoid confusion, so this macro isn't working.

Any help would be greatly appreciated.

Bump no response

Try this macro, it runs automatically from the Worksheet_Change event on Sheet1 (Generator).

Please Login or Register  to view this content.

Many thanks for your help T-J. Unfortunately this code did not work for me. Not sure if you tried it in the attached spreadsheet.

In the end I went for a combo box and attached the second macro in my first post to it. Had to redesign the worksheet as a resutl, but everything works now.

Once again, many thanks.

Cheers,
Carl.

Yes the code worked for me on your spreadsheet, so not sure why it failed for you.
The code is placed in the worksheet code of Sheet1 (Generator).
The code runs when an item from the data validation list in A2 is chosen which then updates the pivot report filter with the value in B2.