Multiple PivotTables and ComboBoxes to select PT and filter

Hi all, I hope that what I’m about to ask doesn’t seem too difficult to picture, but I’ll try to put it out as plainly as I can for you to see what I’m getting at.

I run a Stock Control workbook containing a very large dataset spread over about 20+ worksheets – let’s call it “Stock KPIs – Overview”. Each worksheet contains specific statistics relating to a stock holding key performance indicator (KPI) – for example ‘Return Dues In’, ‘Stock Dues Out’, ‘Lifeexpired Items on shelf’, ‘Incorrect Storage Location’ and so on.

Each of these worksheets contain an auto-filter to help withStock Control Reporting, but the sheets themselves are hidden to prevent everyday users (i.e. not my stock control team) from altering the data in the worksheets.

Here’s where I need the assistance if possible. I want to create a single worksheet in my Stock Control Reporting workbook – called “MA MonthlyStock KPCs” – to contain a user form that, with the addition of two filters, allows the user to select their ‘Stock Site’ and the ‘Report Type’ required. The filtered data from the respective sheet (tied to the ‘Report Type’ filter) is then shown in a list view area, for example; Stock Site: ‘Warehouse 2’; Report Type: ‘Life-Ex Items on Shelf’ shows all lines for that site that are ‘LifeEx. On shelf’ at that location. If at all possible it would also be incredibly useful to be able to print the list view data that had been shown using the filters.

so, here’s where I’m stuck:
1 – Can I use a ComboBox on a User Form to select one of multiple PivotTables?
2 – Once the PT is selected, can I use a 2nd ComboBox to filter that PT?

less important, but

3 – Is it also possible to print the data held in a filtered userform list view?

Once again, really appreciate any assistance you wonderful people might be able to provide.

Hi CK76,

Much appreciated for the swift reply, however 'ease of maintenance' isn't the priority i'm afraid. The reports are auto generated and imported to Excel everyday and the PT's are refreshed ever time the workbook is opened. Also, slicers are not a possibility for my users as we use a mix of Excel 2007 and 2010.

Originally Posted by CK76

By ease of maintenance, I mean code. Any how...

With classic Pivot Table, filtering via code can be time consuming.

As each individual item's visible state must be set, and requires looping. You can read about in thread below (post#6 for sample code)
(link removed)

To pick a pivot table from dropdown, you must also store which sheet the pivottable belongs to. Unlike Table, pivot tables can't be directly referenced using Range("TableName").

Thank you again CK76.

I see what you man regards filtering with VBA being a time consuming business. Would it help at all if I were able to put all Pivot Tables onto a single Worksheet in my workbook. This would cut the VBA code down slightly and make it easier to reference?

Also, if I were to set the 'Stock Site' ComboBox to contain all my Stock Sites using 'ComboBox.AddItem' in VBA, rather than have the ComboBox loop through the selected PT to determine which stock sites were present. I think thereis then a way in VBA to report back if the PivotField on my PT doesn't contain that Stock Site, correct?