+ Reply to Thread
Results 1 to 6 of 6

Multiple PivotTables and ComboBoxes to select PT and filter

  1. #1
    Registered User
    Join Date
    10-26-2012
    Location
    Hampshire
    MS-Off Ver
    Excel 2003
    Posts
    9

    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.

  2. #2
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,726

    Re: Multiple PivotTables and ComboBoxes to select PT and filter

    1 & 2 are both possible. But for ease of maintenance etc. I'd recommend all data be combined in single table (with identifier column for each data set), then base pivot table off of it.

    Then it's simply matter of using slicer to cycle through different data set, then applying additional filter as needed.
    “Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.”
    ― Robert A. Heinlein

  3. #3
    Registered User
    Join Date
    10-26-2012
    Location
    Hampshire
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: Multiple PivotTables and ComboBoxes to select PT and filter

    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.

  4. #4
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,726

    Re: Multiple PivotTables and ComboBoxes to select PT and filter

    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)
    https://www.excelforum.com/excel-gen...in-a-list.html

    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").

  5. #5
    Registered User
    Join Date
    10-26-2012
    Location
    Hampshire
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: Multiple PivotTables and ComboBoxes to select PT and filter

    Quote Originally Posted by CK76 View Post
    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?

  6. #6
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,726

    Re: Multiple PivotTables and ComboBoxes to select PT and filter

    Would it help... put all Pivot Tables onto a single Worksheet...
    Not in terms of speed. Bottle neck is filtering. While it would simplify coding, this is bad practice, as pivot tables usually get expanded or contracted based on user interaction.
    This could cause range to overlap with another pivot table, unless you allocate enough range to each pivot table. Instead, keep a list in some hidden sheet with sheet name and pivot table name.
    Use the list to fill combobox with hidden column for sheet name. Once selection is made, you can read column 2 value for sheet name.
    See thread below for how to read column 2 value in combobox.
    https://www.excelforum.com/excel-pro...ted-value.html

    I think thereis then a way in VBA to report back if the PivotField on my PT doesn't contain that Stock Site, correct?
    Using item not in Pivot table field's item list to filter, will cause error in code. You would need to perform a check yourself in code (using Application.Match(), Dictionary or some other method). Or use error trap to handle it.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Select Automatic Report Filter in Pivot where Filter exist in Data table else select Blank
    By vinaynaran in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-02-2016, 08:47 AM
  2. Filter (hide) using multiple comboboxes
    By jsdba in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-22-2015, 02:56 PM
  3. [SOLVED] ComboBoxes with Select Case
    By excellenthelp in forum Excel General
    Replies: 4
    Last Post: 09-12-2013, 12:51 PM
  4. Find & filter pivottables by a cell value
    By lampoonsaat in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-28-2011, 09:09 AM
  5. Filter several PivotTables using a Cell Value
    By lampoonsaat in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-27-2011, 12:00 PM
  6. Select Cases and Comboboxes
    By AnthonyWB in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-12-2011, 11:13 PM
  7. ++HELP ME PLEASE++ ComboBoxes as a Variable to Select Sheets
    By Adeptus - ExcelForums.com in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 07-10-2005, 12:05 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1