VBA All Slicer Combinations

Learn Excel From MrExcel, Podcast Episode 2106: Create A PDF Of Every Combination Of 3 Slicers.

What a great question we have today. Someone wrote in, wanted to know if it was possible. Right now, they have 3 slicers running a pivot table. I don't know what the pivot table looks like. It's confidential. I’m not allowed to see it so I'm just guessing, right? So, what they're doing is they're choosing one item from each slicer and then creating a PDF, and then going and choosing the next item and creating a PDF, and then the next item, and the next item, and you can imagine, with 400 combinations of slicers, this could take forever, and they said, is there some way to have a program go through and loop through all the options?

I said, alright, here's some qualifying questions. Number one, we're not on a Mac, right? Not Android, not Excel for the iPhone. This is Excel for Windows. Yes, they said. Great. I said, second really important question is, we want to choose one item from a slicer, and then eventually the other item from the slicer, and then the other item from the slicer. We don't need combinations like ANDY, and then ANDY and BETTY, and then ANDY and CHARLIE, right? That’s out. I'm just going to do one item from each slicer. Yes, yes, yes. That's the way it’s going to go. Perfect, I said. So here, tell me this, choose each slicer, go to the SLICER TOOLS, OPTIONS, and go to SLICER SETTINGS. We just did this 2 episodes ago. Isn't this crazy? NAME TO USE IN FORMULAS and I know that it's SLICER_REVIEWER, SLICER_ANTENNA, SLICER_DISCIPLINE, alright? So, I think I've got it.

Now, we're going to switch over to VBA here, and, by the way, make sure that you’re saved as xlsm and make sure your macro security is set to allow macros. If it’s saved as xlsx, trust me, you have to go do a FILE, SAVE AS, you're going to lose all your work if you leave it as xlsx. Yes, 99.9% of the spreadsheets that you use are xlsx but this one with a macro will not work. ALT+F11. Alright, so here’s the code.

We’re going to find three slicer caches, one slicer item, and 3 ranges. For each of the slicer caches, we're going to set it to the name used in formula that I just showed you in the SLICER SETTINGS dialog box. So, we have the three of those. I want to clear all those to make sure that we're back to everything being selected. This counter is going to be used in the file name later.

Alright. Now, this next section here, OFF TO THE RIGHT, BUILD THREE STATIC LISTS OF ALL SLICER ITEMS.  See outtake #2 to see why this craziness had to happen. So I'm going to figure out where the next available column is, kind of go over 2 from the last column, remember that so I can delete the stuff later, and then, for each SI, slicer item, IN SC1.SLICERITEMS, we're going to write that slicer caption to the spreadsheet. When we're done with all of those slicer items, figure out how many rows we had today, and then name that range as SLICERITEMS1. We're going to repeat that whole thing for slicer cache 2, going over 1 column, SLICERITEMS2, and SLICERITEMS3.

Let me show you what it looks like at this point. So, I'll put a breakpoint right here and we will run this code. Alright. That was fast. We're going to switch over to VBA, and far off here to the right-hand side, I'm going to get 3 new lists. These lists are everything that's in the slicer, and you see it's called SLICERITEMS1, SLICERITEMS2, and SLICERITEMS3, alright? We will get rid of that at the end, but that gives us something to loop through. Back to VBA.

Alright. We're going to loop through all the items in SLICERITEMS1, clear the filter for slicer cache 1, and then we're going to go through, one at a time, through each slicer item and see if this slicer item is = to this CELL1.VALUE, and, again, we're looping through each of the values. So, the first time through, it is going to be ANDY and then BETTY and, you know, and so on.

It's frustrating. I couldn't find any way to turn all the slicers off at once. I even tried recording the code and choosing one slicer, and the recorded code was returning 9 slicers off and turning the one slicer on, alright? So frustrating that I couldn't find anything better than that but I couldn't find anything better than that.

So, we set the first slicer = to ANDY. Then we go through, and for the second slicer, we’re going to set it = to the first item. For the third slicer, set it = to the first item.

Alright. Then, down here, DECIDE IF THIS IS A VALID COMBINATION. I’ve got to explain to you why that's important. If we, as humans we're doing this, ANDY, we would not choose A52 because clearly it's grayed out, but the macro is going to be too stupid and it's going to choose A52 and then 104, and it's going to create this empty pivot table. So, there's a thousand possible combinations here. I know that there's only 400 possible reports. That's what the person told me, and so we're going to get 600 times where we're going to create a PDF of this [ugly – 04:45] report.

So, what I'm going to do is I'm going to look here on the ANALYZE tab -- it was called OPTIONS in 2010 -- and see what the name of this pivot table is, and I want to see how many rows we get. In my case, if I get 2 rows, I know it's a report I don’t want to export. If I get more than 2 rows, 3, 4, 5, 6, then I know it is a report that I do want to export. You're going to have to figure out in your situation which it is.

Alright. So, that’s why we're checking to see if the pivot table 2 and, that's the name that was back there in the ribbon, .TABLERANGE2.ROWS.COUNT is > 2. If it's not > 2, we don't want to create a PDF, alright? So, this IF statement down to this END IF is saying we're only going to create the PDFs for the report combinations that have values. MYFILENAME, I created a folder called C:REPORTS. It's just an empty folder. C:REPORTS. You make sure that you have a folder and use the same folder name in the macro. C:REPORTS/ and the name of the file is going to be REPORT001.PDF. Now, counter we initialized back up there is 1 using FORMAT, which is equivalent in Excel to saying the text of counter, and 000. That way, I'm going to get 001, then 002, then 003, and then 004. They're going to sort correctly. If I had just called this REPORT1, and then later on I have a REPORT10 and 11, and later on REPORT100, those are all going to sort together when they don't belong together, alright? So, creating the name of the file in case the file exists from the last time we ran this, we're going to kill it. In other words, delete it. Of course, if you try and kill a file that's not there, they will throw an error. So, if we get an error in the next line, that's fine. Just go on, but then I reset the error checking ON ERROR GOTO 0.

Here’s the ACTIVE SHEET, EXPORT AS FIXED FORMAT, as a PDF, there's the file name, all those choices, and then I increment the counter, so that way, next time we find one that has records, we will be creating REPORT002.PDF. Finish those three loops and then CLEAR OUT THE STATIC LISTS. So, I'll remember which column we were, resize 1 row, 3 columns, ENTIRECOLUMN.CLEAR, and then a nice little message box there to show that things have been created. Okay. Let's run it.

Alright. Now, what should be happening here is if we go and look in Windows Explorer, there it is. Okay. It is creating…like, every second, we're getting 2 or 3 or 4 or more. I'll pause this and let it run. Alright. There we are. 326 reports have been created. It looped through all 1000 possibilities and only kept the ones where there was an actual result. Alright, from 9:38 until 9:42, 4 minutes to do all that, but still faster than doing the 400, alright?

Alright. So, that's the macro way to do this. The other thing that struck me here that it may or may not work. It's really tough to say. Let's take our data and I'm going to move the data to a brand new workbook. MOVE OR COPY, CREATE A COPY, to a NEW BOOK, click OK, and we’re going to use a trick here that I first learned from Szilvia Juhasz – a great Excel consultant out in Southern California -- and we're going to add a KEY field here. The KEY field is = REVIEWER & ANTENNA & DISCIPLINE. We'll copy that down and we'll insert a new pivot table. Click OK, and we're going to take that field, the KEY field, and move it up to the old-fashioned FILTERS, and then let's see. [Let's dispel a little report here with – 08:30] REVIEWER, ANTENNA, DISCIPLINE, and REVENUE, like that.

Alright, now, normally what we would do here is would come open this filter and choose one item from the filter, but the trick from Szilvia is that we can take this pivot table and go to either the ANALYZE tab in ’13 or ’16, or the OPTIONS tab in 2010, open the OPTIONS dropdown, say SHOW REPORT FILTER PAGES, SHOW ALL PAGES OF KEY, and what it's doing right now is it’s inserting a new worksheet for every unique combination of the KEY, probably 300 and some files, alright? Now, how many worksheets can you have in a workbook? Well, that number is different on every computer and it depends on how complicated the workbook is because it's limited by available memory, but here we start on ANDY B37 112. I’m going to press CONTROL and this arrow down to JOE, like that.

The beautiful advantage here is, when I do FILE, EXPORT, CREATE A PDF, and then ALLREPORTS, we’re going to end up with a single PDF with all 326 reports in it. Now, we could have created a single PDF using Adobe Acrobat, select all of these reports, right click, and COMBINE FILES IN ACROBAT, but that requires you have a full version of Acrobat, not just Acrobat Reader.

So, this great trick using SHOW REPORT FILTER PAGES from Szilvia might be a great, great alternative if you have enough memory to create all the versions.

Alright. To learn more about VBA, check out this book Excel 2016 VBA And Macros by Bill Jelen and Tracy Syrstad. That will get you up the VBA learning curve.

Alright. The goal is to loop through all combinations in 3 slicers and generate a PDF for each. Used a little VBA to loop through those slicers. Save as PDF using VBA. The alternate solution there at the end is Szilvia Juhasz’s SHOW REPORT FILTER PAGES and then export the whole thing as PDF.

Hey. I want to thank you for stopping by. We'll see you next time for another netcast from MrExcel.

Well, this will be an outtake. First time I ran this darn thing, I got a 1000 of them, and every darn one of them was Andy A52 104. I'm like what the heck is going on? Except I didn’t say heck.

Alright. So, here, watch this code. This was the code I had. I said I'm going to go through all of the filters FOR EACH SI IN SC1.SLICERITEMS and then I set it = to FALSE, and then the one that I want, I'll set = to TRUE, right? Sound like a great, great bit of code, alright?

So, here's what happens. The first one is Andy, goes away. Betty goes away. Charlie goes away. Dale. Here, I'll just keep pressing F8, F8, F8, F8. I'm down to the last one. This is JOE. I'm about to set JOE = to FALSE and watch what happens over there in Excel. Bam. Once you turn JOE off, it turns them all back on. I mean, that stinks, Excel, and then I would try and turn, what is it, ANDY back on and turning ANDY back on when everybody else is already on. So, it ran through…it created a 1000 of the PDFs, every stinking one. It was ANDY A52 104. It's funny now. It wasn't then.

Alright. Here’s another outtake. Why did I go to the trouble of building the list, the static list, off to the right hand side so I can loop through that static list? Well, originally, I was looping through all of the items in the slicers themselves and it was causing some wrong results. See, here, Andy A52 112 should be 0, but when I actually ran the loop, ANDY A52 112 is showing up with six rows. I’m like, well, that can't be. So, over here, my code, ALT+F11, I put a thing, if SI1.CAPTION=ANDY, SI2.CAPTION=A52, SI3 CAPTION=112, THEN STOP, right? So let's run this code, then stop.

There we are, and I will come back. We should have ANDY A52 112, but when I look, ANDY, it’s not A52, it’s D33. What the heck is going on, and then I come back here, ALT+F11, and I right-click and say that I want to ADD A WATCH, and when I look at this, it claims that the caption is A52 but, very clearly, it's D33. So, is this a bug or am I just violating some weird rule by looping through a collection of 10 items when the order of those 10 items is constantly being reordered? It seems like that must be the problem. Hence, we went with the static list off to the right.

And the third outtake, alright? This is the one that's crazy. If I want to record a macro, if I want to [write a macro – 13:35] to choose just one item, figure out how to do that by using DEVELOPER, RECORD MACRO, HOWTOCHOOSEONEITEMFROMSLICER, click OK, and we simply choose one item. FLO. Click STOP RECORDING, then we go ALT+F8, HOWTOCHOOSEONEITEMFROMSLICER, EDIT that, and, sure enough, they make FLO TRUE and then everybody else FLASE. It means if I had a slicer with a 100 items in it, they would have to put a 100 lines of code in there to unselect everything else. Seems incredibly inefficient but there you are.