Getting Filtered Pivot table in a Pop-Up

[BobZZ]

I am stuck with a certain part of my project. I have included is an excerpt of a bigger project. What I have not been able to figure out is the following:

1) If user is in Evaluation tab and is on a particular part number, he then fires up a macro with a ctrl-z combination to start the macro that filters the pivot table for that part number that he is on.
2) A dialog box/user form or something pops up in the Evaluation tab that shows the contents of the filtered pivot table.
3) When done the user simply closes up the pop-up dialog/user form/whatever mechanism works.

Number 3 I can do, but 1 & 2 is where I don't know where to begin.

[dflak]

I am not quite sure what you want in step 2 - why not just redirect the focus to the pivot table?

I have something that works. It even does multiple selections, but it takes about a minute and a half to run and I have a fairly fast machine. I'll post it if someone can't come up with something better.

[BobZZ]

I guess that might just be the path of least resistance. I wanted to have the information come up in in the 'Evaluation' tab but I guess if I shift focus to the pivot table in another tab then I can do more things. I guess somehow I have to use the hot key to throw me to the pivot table and then back to the Evaluation tab where I was. In pursuing this idea I found some code which I tried but for some reason it is failing for me and I cannot figure out why. Attached is screen shot.
Capture.PNG

[dflak]

Make sure that your sheet names and pivot table names match what's in the code.

This is what I have.

On the evaluation page, select any part numbers you want using the CTRL or SHIFT keys to select multiple parts. Then run the SetFilter Macro. You can assign a key to it.

The program works by assigning a TRUE or False to the selected parts in Column G on the Evaluation Spreadsheet. There is a "helper" pivot table in sheet 1 (which can be hidden). This pivot table has a set of formulas that Mirror the TRUE / FALSE on the valuation sheet. You may have to extend this formula down if you have a lot of part numbers.

This pivot table and helper column are overlaid with a named dynamic range. Every part on the part list is accounted for in this range. The formula determines whether that part number is left turned on or turned off in the pivot table filter.



There is a pivot table on Sheet1 that is a unique listing of part numbers. The Test Macro assigns a TRUE or FALSE next to it