Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: How to link several pivot tables by a Combo box?

  1. #1
    New Member
    Join Date
    Sep 2012
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default How to link several pivot tables by a Combo box?

    Hi there,


    This is my first thread. I hope I post it in the right place...


    In an XLS sheet I have four pivot tables, each of them having as a report filter field "Country".

    I would like to include a dropdown box (combobox) in the sheet where I can select the country. Once a country has been selected, I would like Excel to automatically update the Pivot table filter of all four pivot tables to the selected country.

    Although I managed to do this by creating a combobox with a related Macro (cf. main code below) I am convinced that there is a much smarter way to achieve this. Moreover, my "solution" does not enable me to use " (All) " as selection, i.e. showing all data in the pivot table.

    Here is the "magic macro":

    ActiveSheet.PivotTables("PivotTable1").PivotFields("Country").CurrentPage = Range("G6").Value

    Cell G6 includes the selected country (i.e. result of the combobox)

    Looking forward to receiving your comments.

  2. #2
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    73,092
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: How to link several pivot tables by a Combo box?

    Welcome to MrExcel.

    Depending on your version of Excel you may be able to use Slicers:

    http://www.jkp-ads.com/Articles/slicers02.asp

    Synchronizing PivotTable Filters without using VBA Gerhard Brueckl's BI Blog
    Microsoft MVP - Excel

  3. #3
    New Member
    Join Date
    Sep 2012
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to link several pivot tables by a Combo box?

    Thanks Andrew!

    Slicers functionality seems to deliver exactly what I am looking for. Unfortunately I have to use 2007 for the time being ... waiting for our IT department to roll-out 2010 version.

    Do you by any chance have a non-VBA solution for Excel 2007. In the meantime I found several VBA solutions.

    Thanks again.

  4. #4
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    73,092
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: How to link several pivot tables by a Combo box?

    Sorry, I don't know a non-VBA solution for versions of Excel prior to Excel 2010.
    Microsoft MVP - Excel

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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