VBA---quicker to set pivot table filters or loops through slicers?

[codeliftsleep]

I have a dilemma that I'm faced with. I have 30 slicers(3 each for 10 different sets of pivot tables). I need to update all of the slicers based on the user selecting from a single set of slicers(all of the slicers have the same info from different days).

I have it set up to do this via looping through the slicers and checking to see what is selected and duplicating it via all of the other slicers, except this is taking too long, even after I pretty much made it as efficient as possible and turning off updates, automatic calculations, etc while this is running.

So I was thinking that it might be quicker if I set the current page fields of the pivot fields directly(since this will set the slicers automatically) because I can just choose the ones I need rather than looping through all of them(I think). Is there a way to pass an array of values to the currentpage filter in the pivot table or will I need to set them one by one?

If there is a more efficient way to update multiple slicers/pivot tables with the same values I would appreciate any assistance with it...

 

[codeliftsleep]

Any help with this??

 

[RoryA]

You haven't posted any code so it's hard to comment if there's an obvious quick fix. What kind of pivot tables are they - OLAP or regular?

 

[codeliftsleep]

You haven't posted any code so it's hard to comment if there's an obvious quick fix. What kind of pivot tables are they - OLAP or regular?

I think I've got it about as performant as I can expect at this point. I actually found it was quicker to get rid of loops and variant arrays and just use a string for both the slicer names and items in the slicer and just use InStr to check to see if they were a match. Removed 2 nested loops and prevented the issue where the items get rechecked from looping even though they shouldn't be.

Also took about 4-5 seconds off the entire operation. Was able to get it down to about 20 seconds or so for all 30 slicers(well 27 since it uses the initial 3 for the others)...not great but better than the 2 minutes I was getting when I started out.

 

[RoryA]

I have to wonder what the point of the question was, if you aren't going to help anyone to try and help you?

 

[codeliftsleep]

I have to wonder what the point of the question was, if you aren't going to help anyone to try and help you?

Well at first I was but then I ended up working on it myself and got it done.

Unfortunately I dont always have the luxury of time when I ask questions, I have to figure it out and keep moving.

Now if there is a faster way to set the selected items in the slicer than by looping through them I'd love to hear about it because that could spees things up even more.

I noticed there was a VisibleItemsSelected read/write property on the slicer that can take a variant but I wasnt able to pass an array to it nor get the value from itm.mkept throwing a 450 error saying there was an invakid number of arguments passed but when I tried to pass in the default index it didnt work either...

Is that a possibility?