+ Reply to Thread
Results 1 to 24 of 24

Multiple Pivot Table filter and save

  1. #1
    Forum Contributor
    Join Date
    01-18-2010
    Location
    Bentley, NM
    MS-Off Ver
    Excel 2010
    Posts
    229

    Multiple Pivot Table filter and save

    I found some code that will loop through a pivot table, apply a particular filter, and repeat until all filters have been applied. I need to scale this code to multiple pivot tables located in different Sheets. So the order of operations would be:
    1. Filter PT 1
    2. Filter PT 2
    3. Filter PT 3
    4. Save report as ____
    5. Filter PT 1
    6. Filter PT 2
    7. Filter PT 3
    8. Save report as ____

    Until all filters have been cycled through on each PT. The filter is always on the same field and should always loop the same number of times for each PT, so it's only a matter of making sure it does the same operation for each PT on each Sheet. There are three Sheets that I need to do this for. I don't know if it would be best to reference them by name or if there is another way. Name should work, however.

    Here is the code I'm using to loop through the filters:

    Please Login or Register  to view this content.
    Thanks for any help!

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: Multiple Pivot Table filter and save

    I have this code hanging around in my archives. I think it's close to what you might want. It reads the filter settings on the source page and applies them to the pivot tables on the other sheets.
    Please Login or Register  to view this content.

  3. #3
    Forum Contributor
    Join Date
    01-18-2010
    Location
    Bentley, NM
    MS-Off Ver
    Excel 2010
    Posts
    229

    Re: Multiple Pivot Table filter and save

    Thanks, this looks like it could have some helpful info. However, for a VBA noob such as myself it's a little much for me to follow and edit for my purposes. I was thinking I could just edit "my code" with another For Loop that would loop through the sheets. However, I'm not sure how to do that. I'll keep plugging away and try to understand your code a bit better.

  4. #4
    Forum Contributor
    Join Date
    01-18-2010
    Location
    Bentley, NM
    MS-Off Ver
    Excel 2010
    Posts
    229

    Re: Multiple Pivot Table filter and save

    Revisiting this a month or so later. Can anyone chime in on how to edit the original code posted to loop through each Worksheet? I wasn't able to edit dflak's code for my needs. Thanks.

  5. #5
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: Multiple Pivot Table filter and save

    Just to confirm: You want to set the filters on one pivot table and have it set the same set of filters in the rest of the pivot tables. Is this correct?
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  6. #6
    Forum Contributor
    Join Date
    01-18-2010
    Location
    Bentley, NM
    MS-Off Ver
    Excel 2010
    Posts
    229

    Re: Multiple Pivot Table filter and save

    Quote Originally Posted by dflak View Post
    Just to confirm: You want to set the filters on one pivot table and have it set the same set of filters in the rest of the pivot tables. Is this correct?
    Actually, I don't want to set any filter manually. I will tell the code which field to filter the PTs on and then the code needs to filter all 4 PTs by that field -> perform some other operations -> then loop back through and filter the PTs again by the next value of the same field. So there is only one field to filter the PTs on, but there are multiple values for the field. The end goal is to filter all the PTs by the same value, save the workbook, and repeat until all values are gone through.

  7. #7
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: Multiple Pivot Table filter and save

    Would it be possible to attach a sample workbook? Take the source data and do a find and replace on sensitive data and replace with dummy values.

    Also let me rephrase the requirement to see if I got it. You want to find the first value for the filter, set all the filters and save the workbook under some name. Then move onto the next value in the filter do the same thing and save the workbook under a different name.

  8. #8
    Forum Contributor
    Join Date
    01-18-2010
    Location
    Bentley, NM
    MS-Off Ver
    Excel 2010
    Posts
    229

    Re: Multiple Pivot Table filter and save

    Quote Originally Posted by dflak View Post
    Would it be possible to attach a sample workbook? Take the source data and do a find and replace on sensitive data and replace with dummy values.

    Also let me rephrase the requirement to see if I got it. You want to find the first value for the filter, set all the filters and save the workbook under some name. Then move onto the next value in the filter do the same thing and save the workbook under a different name.
    The sample workbook might be a little difficult, but I will work on it.

    I think you've got it. The code below will cycle through all the available values for the "Provider Group" field that I'm filtering on for one Pivot Table. But, I need to instead only select the first value, move to the next sheet and apply the same value, etc. through all of the sheets. After the last sheet I need to save the workbook (I can handle that part ) and then start the process over for the next "Provider Group" value.

    Please Login or Register  to view this content.

  9. #9
    Forum Contributor
    Join Date
    01-18-2010
    Location
    Bentley, NM
    MS-Off Ver
    Excel 2010
    Posts
    229

    Re: Multiple Pivot Table filter and save

    Here is a sample workbook. Notice that each PT has the same Provider Group field with the same values. Now that we have real data the steps would be:

    1. Filter Sheet1 PT by Provider Group 'elisa'
    2. Filter Sheet2 PT by Provider Group 'elisa'
    3. Filter Sheet3 PT by Provider Group 'elisa'
    4. Filter Sheet4 PT by Provider Group 'elisa'
    5. Perform some additional actions (save workbook, etc.)
    6. Filter Sheet1 PT by Provider Group 'frank'
    7. Filter Sheet2 PT by Provider Group 'frank'
    8. Filter Sheet3 PT by Provider Group 'frank'
    9. Filter Sheet4 PT by Provider Group 'frank'
    10. Perform some additional actions (save workbook, etc.)
    11. Repeat for each Provider Group

    Pivot Table example.xlsx

  10. #10
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: Multiple Pivot Table filter and save

    I have a question. Do you just want the line of data with each person, or does filtering out the other people affect the calculations on the pivot table?

    I'll assume the former unless I hear otherwise.

  11. #11
    Forum Contributor
    Join Date
    01-18-2010
    Location
    Bentley, NM
    MS-Off Ver
    Excel 2010
    Posts
    229

    Re: Multiple Pivot Table filter and save

    Oops, yes we can only show Elisa's data to Elisa, so the others must not be visible.

  12. #12
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: Multiple Pivot Table filter and save

    The reason I asked the question was that I had a method that reset the filters, bit it was extremely slow. This method is simpler. It loops through the sheets and finds the pivot table. It sets a range (pt.RowNames) that contains the names. It looks through the list of names (For Each pi In pf.PivotItems) and finds the row on which that range is found. I get that data (Intersect(FRange.EntireRow, pt.TableRange1)) and in my case, it copies it over a couple of rows. I did this simply for the sake of QA. This is where you would insert your code.

    Copy the code below into a module and have fun

    This is my Bible when it comes to pivot tables: http://peltiertech.com/referencing-p...ranges-in-vba/.
    Please Login or Register  to view this content.

  13. #13
    Forum Contributor
    Join Date
    01-18-2010
    Location
    Bentley, NM
    MS-Off Ver
    Excel 2010
    Posts
    229

    Re: Multiple Pivot Table filter and save

    Thanks! So, when I filter by 'Elisa' for example, I want to only see her listed in the pivot table on each sheet. At that point I know I can save the workbook into Elisa's folder and move on to Frank. This is so that they can't see each other's info. Wish I could troubleshoot this more, but my skills are limited. Can we combine your code with the code I pasted above that filters and hides?

  14. #14
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: Multiple Pivot Table filter and save

    Actually, if you copy the book then Elisa can still see Frank. All she has to do is change the filter. If you want more security then what I would do is copy her information to a working sheet, and then save that sheet to a workbook and copy that workbook over.

    We might be able to take this one step further. Instead of sending 5 sheets to everyone, send them one consolidated sheet.

    I'll send a sample shortly.

  15. #15
    Forum Contributor
    Join Date
    01-18-2010
    Location
    Bentley, NM
    MS-Off Ver
    Excel 2010
    Posts
    229

    Re: Multiple Pivot Table filter and save

    Quote Originally Posted by dflak View Post
    Actually, if you copy the book then Elisa can still see Frank. All she has to do is change the filter. If you want more security then what I would do is copy her information to a working sheet, and then save that sheet to a workbook and copy that workbook over.

    We might be able to take this one step further. Instead of sending 5 sheets to everyone, send them one consolidated sheet.

    I'll send a sample shortly.
    Sorry, I should have been more upfront with requirements etc. What I do currently is apply the filter and then lock the sheets down and the VBA with a password. Sicne the data isn't extra super duper sensitive I can get away with this. So, I filter each sheet, lock down all the sheets, save to the Provider Group folder.

    Unfortunately one consolidated sheet won't work because there are something like 20 + columns of data on each sheet. It's big and ugly.

  16. #16
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: Multiple Pivot Table filter and save

    Would it make a difference if the filter were a pivot table filter and not a row filter as in the picture?

  17. #17
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: Multiple Pivot Table filter and save

    Would it make a difference if the filter were on the whole pivot table instead of on the rows like the picture?
    Attached Images Attached Images

  18. #18
    Forum Contributor
    Join Date
    01-18-2010
    Location
    Bentley, NM
    MS-Off Ver
    Excel 2010
    Posts
    229

    Re: Multiple Pivot Table filter and save

    I think that is preferable actually - to filter on the pivot table and not the rows. Did you see the code I posted before? It basically just sets one Provider Group to visible and the rest to hidden.

  19. #19
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: Multiple Pivot Table filter and save

    Try this. Its weakness is that if someone doesn't have metrics on one of the pages, it will break.
    Please Login or Register  to view this content.

  20. #20
    Forum Contributor
    Join Date
    01-18-2010
    Location
    Bentley, NM
    MS-Off Ver
    Excel 2010
    Posts
    229

    Re: Multiple Pivot Table filter and save

    Working for the sample data! I will give it a go later with the real stuff and let you know. Really appreciate it.

  21. #21
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: Multiple Pivot Table filter and save

    It should work no matter where the filter is. The only thing it doesn't do is check to see if a person might not appear on one of the pivot tables. If this happens, you will get an error. It also assumes everyone appears on the first pivot table. There are ways around both of these issues if it is necessary.

  22. #22
    Forum Contributor
    Join Date
    01-18-2010
    Location
    Bentley, NM
    MS-Off Ver
    Excel 2010
    Posts
    229

    Re: Multiple Pivot Table filter and save

    Quote Originally Posted by dflak View Post
    It should work no matter where the filter is. The only thing it doesn't do is check to see if a person might not appear on one of the pivot tables. If this happens, you will get an error. It also assumes everyone appears on the first pivot table. There are ways around both of these issues if it is necessary.
    Sounds great. If a person is missing from the first pivot table they will be missing from all, so it shouldn't be an issue. I tested with my actual data and it worked great. Only thing is that it's rather slow. This isn't a problem for me, but I thought I would let you know. Thanks a ton for your help. You have just saved me hours or work.

  23. #23
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: Multiple Pivot Table filter and save

    One thing I didn't do is turn off calculations.

    PivotTable.ManualUpdate = True

    at the end setting the filters refresh the tables with:

    Please Login or Register  to view this content.
    Throw in a ClearPivot at the end of the loop.

    If the filter was a report filter instead of a row filter, it may go faster. Then all I'd have to do is set the cell with the filter to the person's name, and you are on your way.
    Last edited by dflak; 02-15-2016 at 03:53 PM.

  24. #24
    Forum Contributor
    Join Date
    01-18-2010
    Location
    Bentley, NM
    MS-Off Ver
    Excel 2010
    Posts
    229

    Re: Multiple Pivot Table filter and save

    Actually it goes perfectly fast when it's the only workbook open. It may be recalculating the other open books. Seems to be working flawlessly right now. This post should help a lot of people trying to do the same thing. I found a lot of similar ideas, but nothing that would do exactly what I needed.

+ 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. Using Pivot table to filter across multiple worksheets?
    By jtmegerian in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 06-15-2015, 03:36 PM
  2. Pivot Table : getting count of multiple value by filter
    By viridian in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 03-07-2014, 03:26 PM
  3. multiple label filter in pivot table
    By pma85 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 08-23-2013, 08:45 AM
  4. Using multiple filter for Pivot table
    By shan.hunk in forum Excel General
    Replies: 0
    Last Post: 08-01-2012, 05:56 PM
  5. Change Pivot table Filter Based on Cell Value *Multiple Filter items* Possible?
    By Flydd in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-27-2012, 06:57 AM
  6. Multiple Pivot Table Filter on Cell Text
    By jsegner in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-13-2012, 05:52 PM
  7. filter pivot table with multiple values?
    By hamsup1o in forum Excel General
    Replies: 0
    Last Post: 09-29-2009, 04:08 PM

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